Partners want to find out exactly what they want to see MySQL article ？ here → MySQL Column catalog | Click here

  MySQL Transfer line column , For students who often process data , It must be no stranger , Even impressive , Because it probably bothers you , Let you be stunned ~ But when you see this article , This problem is no longer a problem , Timely collection , Who will ask you this question later , Just throw it in his face , Paste and use .

   Here's a little secret , Actually, me and 《 Little joy 》 Are you classmates or good friends ~~ Today, I'll take some of our brother's college entrance examination scores as the test table . Um. , Believe it, brothers ？ I also have a group photo of us at school , I haven't been in senior three yet PS, So the picture must be true ！

I don't say much nonsense , First , Let's take a look at our Test table data and Expected query results .

mysql> SELECT * FROM t_gaokao_score;
+----+--------------+--------------+-------+
| id | student_name | subject      | score |
+----+--------------+--------------+-------+
|  1 |  Lin Leier        |  Chinese language and literature          |   148 |
|  2 |  Lin Leier        |  mathematics          |   150 |
|  3 |  Lin Leier        |  English          |   147 |
|  4 |  Joe yingzi        |  Chinese language and literature          |   121 |
|  5 |  Joe yingzi        |  mathematics          |   106 |
|  6 |  Joe yingzi        |  English          |   146 |
|  7 |  Fang Yifan        |  Chinese language and literature          |    70 |
|  8 |  Fang Yifan        |  mathematics          |    90 |
|  9 |  Fang Yifan        |  English          |    59 |
| 10 |  Fang Yifan        |  Extra points for Specialty      |   200 |
| 11 |  Chen ha ha        |  Chinese language and literature          |   109 |
| 12 |  Chen ha ha        |  mathematics          |    92 |
| 13 |  Chen ha ha        |  English          |    80 |
+----+--------------+--------------+-------+
13 rows in set (0.00 sec)

   
    

   
    

     
1
     
2
     
3
     
4
     
5
     
6
     
7
     
8
     
9
     
10
     
11
     
12
     
13
     
14
     
15
     
16
     
17
     
18
     
19
    

Look at us. Transfer line column Results after conversion :

+--------------+--------+--------+--------+--------------+
| student_name |  Chinese language and literature    |  mathematics    |  English    |  Extra points for Specialty      |
+--------------+--------+--------+--------+--------------+
|  Lin Leier        |    148 |    150 |    147 |            0 |
|  Joe yingzi        |    121 |    106 |    146 |            0 |
|  Fang Yifan        |     70 |     90 |     59 |          200 |
|  Chen ha ha        |    109 |     92 |     80 |            0 |
+--------------+--------+--------+--------+--------------+
4 rows in set (0.00 sec)

   
    

   
    

     
1
     
2
     
3
     
4
     
5
     
6
     
7
     
8
     
9
    

   good , Let's take a look SQL How it was written , by the way , Creating table structure and importing test data SQL Put it at the end of the article , Self taking ~

---

---

One 、 Transfer line column SQL How to write it

• Method 1 、 Use case..when..then Conduct Transfer line column

SELECT student_name,
	SUM(CASE `subject` WHEN ' Chinese language and literature ' THEN score ELSE 0 END) as ' Chinese language and literature ',
	SUM(CASE `subject` WHEN ' mathematics ' THEN score ELSE 0 END) as ' mathematics ',
	SUM(CASE `subject` WHEN ' English ' THEN score ELSE 0 END) as ' English ',
	SUM(CASE `subject` WHEN ' Extra points for Specialty ' THEN score ELSE 0 END) as ' Extra points for Specialty ' 
FROM t_gaokao_score 
GROUP BY student_name;

   
    

   
    

     
1
     
2
     
3
     
4
     
5
     
6
     
7
    

   If not used here SUM() Will be submitted to the sql_mode=only_full_group_by Related errors , need Aggregate functions and group by Continuous use or Use distinct To solve the problem .

   Actually , added SUM() To be able to use GROUP BY according to student_name Grouping , every last student_name Corresponding subject=" Chinese language and literature " After all, there is only one record , therefore SUM() The value of is equal to that of the corresponding record score Value . Of course , It can also be replaced by MAX().

• Method 2 、 Use IF() Conduct Transfer line column ：

SELECT student_name,
	SUM(IF(`subject`=' Chinese language and literature ',score,0)) as ' Chinese language and literature ',
	SUM(IF(`subject`=' mathematics ',score,0)) as ' mathematics ',
	SUM(IF(`subject`=' English ',score,0)) as ' English ',
	SUM(IF(`subject`=' Extra points for Specialty ',score,0)) as ' Extra points for Specialty ' 
FROM t_gaokao_score 
GROUP BY student_name;

   
    

   
    

     
1
     
2
     
3
     
4
     
5
     
6
     
7
    

   This method will IF(subject=' Chinese language and literature ',score,0) As a condition , adopt student_name Grouping , All after grouping subject=' Chinese language and literature ’ The record of score The fields go SUM() operation , If score If there is no value, the default value is 0.

   This way and case..when..then The principle of the method is the same , More concise than , It is recommended to use .

Two 、 If leadership @ you , Let you add the total sequence to the result set ？

Friendship tips ： When we deal with row to column data in our work , Try to count the total 、 Average, etc. plus , Convenient for leaders to consult , Save him circulation BB you .

Words , Do you remember what the grade sheet was like at school ？ Do you usually look from top to bottom or from bottom to top ？ Voting at the end of the text , Come and have fun for everyone ！

How to write it ： utilize SUM(IF()) Generate columns ,WITH ROLLUP Generate summary columns and rows , And make use of IFNULL Display the total line title as total

SELECT IFNULL(student_name,' total ') AS student_name,
	SUM(IF(`subject`=' Chinese language and literature ',score,0)) AS ' Chinese language and literature ',
	SUM(IF(`subject`=' mathematics ',score,0)) AS ' mathematics ',
	SUM(IF(`subject`=' English ',score,0)) AS ' English ',
	SUM(IF(`subject`=' Extra points for Specialty ',score,0)) AS ' Extra points for Specialty ',
	SUM(score) AS ' total ' 
FROM t_gaokao_score
GROUP BY student_name WITH ROLLUP;

   
    

   
    

     
1
     
2
     
3
     
4
     
5
     
6
     
7
     
8
    

Query results ：

+--------------+--------+--------+--------+--------------+--------+
| student_name |  Chinese language and literature    |  mathematics    |  English    |  Extra points for Specialty      |  total    |
+--------------+--------+--------+--------+--------------+--------+
|  Joe yingzi        |    121 |    106 |    146 |            0 |    373 |
|  Fang Yifan        |     70 |     90 |     59 |          200 |    419 |
|  Lin Leier        |    148 |    150 |    147 |            0 |    445 |
|  Chen ha ha        |    113 |    116 |     80 |            0 |    309 |
|  total          |    452 |    462 |    432 |          200 |   1546 |
+--------------+--------+--------+--------+--------------+--------+
5 rows in set, 1 warning (0.00 sec)

   
    

   
    

     
1
     
2
     
3
     
4
     
5
     
6
     
7
     
8
     
9
     
10
    

3、 ... and 、 Once again, the leadership is double. @ You need to change

   Let you convert the score into specific content display （ good 、 good 、 Ordinary 、 Bad ）,430 Key universities with scores above ,400 Score more than one book ,350 Two or more copies ,350 Move bricks below , How to write ？

   It's disgusting , Don't say the , Xiangan rice ~~（ A unique skill ： Fan Dun ）

   Here we need case when nesting There you go , Look at the tall , It's just ordinary nesting . Find out the scores of each subject after grouping on the first floor , Replace it with grade on the second floor .

SELECT student_name,
MAX(  
        CASE subject  
        WHEN ' Chinese language and literature ' THEN  
            (  
                CASE  
                WHEN score - (select avg(score) from t_gaokao_score where subject=' Chinese language and literature ') > 20 THEN  
                    ' good '  
                WHEN score - (select avg(score) from t_gaokao_score where subject=' Chinese language and literature ') > 10 THEN  
                    ' good '  
                WHEN score - (select avg(score) from t_gaokao_score where subject=' Chinese language and literature ') >= 0 THEN  
                    ' Ordinary '  
                ELSE  
                    ' Bad '  
                END  
            )  
        END  
    ) as ' Chinese language and literature ', 
MAX(  
        CASE subject  
        WHEN ' mathematics ' THEN  
            (  
                CASE  
                WHEN score - (select avg(score) from t_gaokao_score where subject=' mathematics ') > 20 THEN  
                    ' good '  
                WHEN score - (select avg(score) from t_gaokao_score where subject=' mathematics ') > 10 THEN  
                    ' good '  
                WHEN score - (select avg(score) from t_gaokao_score where subject=' mathematics ') >= 0 THEN  
                    ' Ordinary '  
                ELSE  
                    ' Bad '  
                END  
            )  
        END  
    ) as ' mathematics ',
MAX(  
        CASE subject  
        WHEN ' English ' THEN  
            (  
                CASE  
                WHEN score - (select avg(score) from t_gaokao_score where subject=' English ') > 20 THEN  
                    ' good '  
                WHEN score - (select avg(score) from t_gaokao_score where subject=' English ') > 10 THEN  
                    ' good '  
                WHEN score - (select avg(score) from t_gaokao_score where subject=' English ') >= 0 THEN  
                    ' Ordinary '  
                ELSE  
                    ' Bad '  
                END  
            )  
        END  
    ) as ' English ',
SUM(score) as ' Total score ',
(CASE WHEN SUM(score) > 430 THEN ' Key universities '  
	  WHEN SUM(score) > 400 THEN ' a copy '  
	  WHEN SUM(score) > 350 THEN ' Two copies '  
	  ELSE ' Move bricks at the construction site ' 
	  END ) as ' result '
FROM t_gaokao_score 
GROUP BY student_name 
ORDER BY SUM(score) desc;

   
    

   
    

     
1
     
2
     
3
     
4
     
5
     
6
     
7
     
8
     
9
     
10
     
11
     
12
     
13
     
14
     
15
     
16
     
17
     
18
     
19
     
20
     
21
     
22
     
23
     
24
     
25
     
26
     
27
     
28
     
29
     
30
     
31
     
32
     
33
     
34
     
35
     
36
     
37
     
38
     
39
     
40
     
41
     
42
     
43
     
44
     
45
     
46
     
47
     
48
     
49
     
50
     
51
     
52
     
53
     
54
     
55
     
56
     
57
     
58
     
59
     
60
     
61
    

Let's take a look at the output ：

+--------------+--------+--------+--------+--------+--------------+
| student_name |  Chinese language and literature    |  mathematics    |  English    |  Total score    |  result          |
+--------------+--------+--------+--------+--------+--------------+
|  Lin Leier        |  good    |  good    |  good    |    445 |  Key universities      |
|  Fang Yifan        |  Bad      |  Bad      |  Bad      |    419 |  a copy          |
|  Joe yingzi        |  Ordinary    |  Bad      |  good    |    373 |  Two copies          |
|  Chen ha ha        |  Ordinary    |  Ordinary    |  Bad      |    309 |  Move bricks at the construction site      |
+--------------+--------+--------+--------+--------+--------------+
4 rows in set (0.00 sec)

   
    

   
    

     
1
     
2
     
3
     
4
     
5
     
6
     
7
     
8
     
9
    

From people's experience , Honest children suffer the most , I knew I'd go to art and sports ~

Four 、 Conclusion

   Okay ,SQL These are the above contents , Questions can be written in the comments area , Ha Ge will reply you when fishing ~~`

   Help three times , finger heart ღ( ´･ᴗ･` )

appendix ： Create a table structure & Test data SQL

Table structure ：

DROP TABLE IF EXISTS `t_gaokao_score`;
CREATE TABLE `t_gaokao_score`  (
  `id` int(0) NOT NULL AUTO_INCREMENT,
  `student_name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT ' The student's name ',
  `subject` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT ' subject ',
  `score` double NULL DEFAULT NULL COMMENT ' achievement ',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 11 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

   
    

   
    

     
1
     
2
     
3
     
4
     
5
     
6
     
7
     
8
    

Import test data

INSERT INTO `t_gaokao_score` VALUES 
(1, ' Lin Leier ', ' Chinese language and literature ', 148),
(2, ' Lin Leier ', ' mathematics ', 150),
(3, ' Lin Leier ', ' English ', 147),
(4, ' Joe yingzi ', ' Chinese language and literature ', 121),
(5, ' Joe yingzi ', ' mathematics ', 106),
(6, ' Joe yingzi ', ' English ', 146),
(7, ' Fang Yifan ', ' Chinese language and literature ', 70),
(8, ' Fang Yifan ', ' mathematics ', 90),
(9, ' Fang Yifan ', ' English ', 59),
(10, ' Fang Yifan ', ' Extra points for Specialty ', 200),
(11, ' Chen ha ha ', ' Chinese language and literature ', 109),
(12, ' Chen ha ha ', ' mathematics ', 92),
(13, ' Chen ha ha ', ' English ', 80);

   
    

   
    

     
1
     
2
     
3
     
4
     
5
     
6
     
7
     
8
     
9
     
10
     
11
     
12
     
13
     
14