Do you want to accurately find the MySQL articles you want to read? Here → MySQL column directory | click here
MySQL row column conversion must be familiar or even impressive to students who often process data, because it probably bothers you and makes you stunned ~ but when you see this article, this problem is no longer a problem. Collect it in time. Who will ask you this question in the future, throw it on his face and paste it immediately.
here's a little secret. In fact, I and some of the students in little joy are classmates or good friends ~ ~ today I'll take some of our brother's college entrance examination scores as the test table. Well, don't you believe it, brothers? I also have our group photos in school. There was no PS in senior three, so the photos must be true! ππ
No more nonsense. First, let's take a look at our test table data and the expected query results.
mysql> SELECT * FROM t_gaokao_score; +----+--------------+--------------+-------+ | id | student_name | subject | score | +----+--------------+--------------+-------+ | 1 | Lin Leier | language | 148 | | 2 | Lin Leier | mathematics | 150 | | 3 | Lin Leier | English | 147 | | 4 | Qiao yingzi | language | 121 | | 5 | Qiao yingzi | mathematics | 106 | | 6 | Qiao yingzi | English | 146 | | 7 | Fang Yifan | language | 70 | | 8 | Fang Yifan | mathematics | 90 | | 9 | Fang Yifan | English | 59 | | 10 | Fang Yifan | Extra points for Specialty | 200 | | 11 | Ha ha Chen | language | 109 | | 12 | Ha ha Chen | mathematics | 92 | | 13 | Ha ha Chen | English | 80 | +----+--------------+--------------+-------+ 13 rows in set (0.00 sec)
Take a look at the results after our row column conversion:
+--------------+--------+--------+--------+--------------+ | student_name | language | mathematics | English | Extra points for Specialty | +--------------+--------+--------+--------+--------------+ | Lin Leier | 148 | 150 | 147 | 0 | | Qiao yingzi | 121 | 106 | 146 | 0 | | Fang Yifan | 70 | 90 | 59 | 200 | | Ha ha Chen | 109 | 92 | 80 | 0 | +--------------+--------+--------+--------+--------------+ 4 rows in set (0.00 sec)
OK, let's take a look at how SQL is written. By the way, the SQL for creating table structure and importing test data is put at the end of the article, self fetching~
1, Row to column SQL writing method
- Method 1: use case when.. Then row to column
SELECT student_name, SUM(CASE `subject` WHEN 'language' THEN score ELSE 0 END) as 'language', 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;
if SUM() is not used here, SQL will be reported_ mode=only_ full_ group_ By related errors can only be solved by using the aggregate function in conjunction with group by or using distinct.
in fact, SUM() is added so that GROUP BY can be used according to student_name to group each student_ After all, there is only one record of subject = "language" corresponding to name, so the value of SUM() is equal to the score value of the corresponding record. Of course, you can also change to MAX().
- Method 2: use IF() to transfer rows to columns:
SELECT student_name, SUM(IF(`subject`='language',score,0)) as 'language', 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;
this method takes IF(subject = 'Chinese', score,0) as the condition through student_name is grouped. SUM() is performed on the score field of all records with subject = 'language' after grouping. If the score has no value, it is 0 by default.
this way and case when.. The principle of then method is the same, which is more concise and clear, and it is recommended to use.
2, What if you lead @ you to add the total series to the result set?
Friendly tip: when processing row to column data in our work, we try to add the total number and average to facilitate the leader's reference and save him from circulating BB you.
In other words, do you remember what the school grade sheet was like? Do you usually look from top to bottom or from bottom to top? Vote at the end of the text. Come and have fun!
Writing method: use SUM(IF()) to generate columns, WITH ROLLUP to generate summary columns and rows, and use IFNULL to display the header of the summary row as the total number
SELECT IFNULL(student_name,'total') AS student_name, SUM(IF(`subject`='language',score,0)) AS 'language', 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;
Query results:
+--------------+--------+--------+--------+--------------+--------+ | student_name | language | mathematics | English | Extra points for Specialty | total | +--------------+--------+--------+--------+--------------+--------+ | Qiao yingzi | 121 | 106 | 146 | 0 | 373 | | Fang Yifan | 70 | 90 | 59 | 200 | 419 | | Lin Leier | 148 | 150 | 147 | 0 | 445 | | Ha ha Chen | 113 | 116 | 80 | 0 | 309 | | total | 452 | 462 | 432 | 200 | 1546 | +--------------+--------+--------+--------+--------------+--------+ 5 rows in set, 1 warning (0.00 sec)
Three, once again, leadership needs to be changed.
let you convert your score into specific content display (excellent, good, ordinary, poor), key universities with more than 430 points, one book with more than 400 points, two books with more than 350 points, and moving bricks below 350. How should you write?
it's disgusting. Stop talking and eat first πΆπΆ~~ (unique skill: Fan Dun)
here we need case when nesting. Looking at the tall, it is actually just ordinary nesting. Find out the scores of each subject after grouping on the first layer and replace them with grades on the second layer.
SELECT student_name,
MAX(
CASE subject
WHEN 'language' THEN
(
CASE
WHEN score - (select avg(score) from t_gaokao_score where subject='language') > 20 THEN
'excellent'
WHEN score - (select avg(score) from t_gaokao_score where subject='language') > 10 THEN
'good'
WHEN score - (select avg(score) from t_gaokao_score where subject='language') >= 0 THEN
'ordinary'
ELSE
'difference'
END
)
END
) as 'language',
MAX(
CASE subject
WHEN 'mathematics' THEN
(
CASE
WHEN score - (select avg(score) from t_gaokao_score where subject='mathematics') > 20 THEN
'excellent'
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
'difference'
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
'excellent'
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
'difference'
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 'Brick moving on site'
END ) as 'result'
FROM t_gaokao_score
GROUP BY student_name
ORDER BY SUM(score) desc;
Let's take a look at the output:
+--------------+--------+--------+--------+--------+--------------+ | student_name | language | mathematics | English | Total score | result | +--------------+--------+--------+--------+--------+--------------+ | Lin Leier | excellent | excellent | excellent | 445 | Key universities | | Fang Yifan | difference | difference | difference | 419 | a copy | | Qiao yingzi | ordinary | difference | excellent | 373 | Two copies | | Ha ha Chen | ordinary | ordinary | difference | 309 | Brick moving on site | +--------------+--------+--------+--------+--------+--------------+ 4 rows in set (0.00 sec)
From the experience of people who came here, honest children suffer the most. They already knew that they would take art and sports~
4, Conclusion
well, that's all about SQL. If you have any questions, you can write them in the comment area. Hago will reply you when fishing~~`
help me connect three times, Bixin (´ α΄α¦ `)
Appendix: create 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 'Student 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;
Import test data
INSERT INTO `t_gaokao_score` VALUES (1, 'Lin Leier', 'language', 148), (2, 'Lin Leier', 'mathematics', 150), (3, 'Lin Leier', 'English', 147), (4, 'Qiao yingzi', 'language', 121), (5, 'Qiao yingzi', 'mathematics', 106), (6, 'Qiao yingzi', 'English', 146), (7, 'Fang Yifan', 'language', 70), (8, 'Fang Yifan', 'mathematics', 90), (9, 'Fang Yifan', 'English', 59), (10, 'Fang Yifan', 'Extra points for Specialty', 200), (11, 'Ha ha Chen', 'language', 109), (12, 'Ha ha Chen', 'mathematics', 92), (13, 'Ha ha Chen', 'English', 80);