SQL query--horizontal and vertical comparison of the same table

table name : Student

table structure and data :

+----+--------+---------+------+------------+--------------+---------+
| ID | name | 中文版 | Math | Birthday | Native_place | Chinese |
+----+--------+---------+------+------------+--------------+---------+
| 1 |      Pan Yiju |   86 | 91 | 1990-01-01 |      Shanghai | 97 |
| 2 |      Liu Jingsong |   88 | 68 | 1990-02-01 |      Shanghai | 96 |
| 3 |      Liu Ji such as |   85 | 53 | 1990-03-01 |      Shanghai | 70 |
| 4 |      Li Yanke |   85 | 70 | 1990-04-01 |      Shanghai | 96 |
| 5 |      Wang Xiaobo |   85 | 79 | 1990-05-01 |      Shanghai | 46 |
| 6 |      Li Yunxu |   79 | 76 | 1990-06-01 |      Shanghai | 97 |
| 7 |      Li Jingyao |   89 | 61 | 1990-07-01 |      Shanghai | 92 |
| 8 |      Kim Bailing van |   80 | 43 | 1990-08-01 |      Shanghai | 83 |
| 9 |      Qin Zi Airlines |   57 | 46 | 1990-09-01 |      Shanghai | 86 |
| 10 |      Guan Yingli |   80 | 77 | 1991-01-01 |      Shanghai | 84 |
+----+--------+---------+------+------------+--------------+---------+

Requirements : Find the name of the partial section student in the table.

Analysis :

There are three subjects in this table: English, Math, Chinese

1-Calculate the fractional difference of the three subjects (for absolute value)--using ABS (Math-english) ABS (Chinese-english) ABS (Math-chinese)

2-Find out the difference between mathematics and English in the whole class, the difference between Chinese and English scores, the maximum value of the difference between math and English--max (ABS (Math-english)), Max (ABS (Chinese-english)), M AX (ABS (Math-chinese))

3-finds the maximum value in the 2 result

4-Search in the student table

The difference between math and English is equal to (3-the result)

The difference in scores between Chinese and English equals (3-the result)

The difference between math and English is equal to (3-the result)

This allows you to find the name of the partial section student.

SQL code :

Select name from student
where
ABS (Chinese-math) in (select Max (Tmp.score)
From (
Select MAX (ABS (S1.chinese-s1.math)) score from student S1
Union SELECT MAX (ABS (s2.chinese-s2.english)) score from student S2
Union SELECT MAX (ABS (S3.english-s3.math)) score from student S3
) TMP)
Or
ABS (Chinese-english) in (select Max (Tmp1.score)
From (
Select MAX (ABS (S1.chinese-s1.math)) score from student S1
Union SELECT MAX (ABS (s2.chinese-s2.english)) score from student S2
Union SELECT MAX (ABS (S3.english-s3.math)) score from student S3
) TMP1)
Or
ABS (English-math) in (select Max (Tmp2.score)
From (
Select MAX (ABS (S1.chinese-s1.math)) score from student S1
Union SELECT MAX (ABS (s2.chinese-s2.english)) score from student S2
Union SELECT MAX (ABS (S3.english-s3.math)) score from student S3
) TMP2);

SQL query--horizontal and vertical comparison of the same table