5分鐘搞懂MySQL - 行轉(zhuǎn)列
#創(chuàng)建表結(jié)構(gòu)
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 '學(xué)生姓名',
`subject` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '科目',
`score` double NULL DEFAULT NULL COMMENT '成績(jī)',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 11 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;#導(dǎo)入測(cè)試數(shù)據(jù)INSERT INTO `t_gaokao_score` VALUES
(1, '林磊兒', '語(yǔ)文', 148),
(2, '林磊兒', '數(shù)學(xué)', 150),
(3, '林磊兒', '英語(yǔ)', 147),
(4, '喬英子', '語(yǔ)文', 121),
(5, '喬英子', '數(shù)學(xué)', 106),
(6, '喬英子', '英語(yǔ)', 146),
(7, '方一凡', '語(yǔ)文', 70),
(8, '方一凡', '數(shù)學(xué)', 90),
(9, '方一凡', '英語(yǔ)', 59),
(10, '方一凡', '特長(zhǎng)加分', 200),
(11, '陳哈哈', '語(yǔ)文', 109),
(12, '陳哈哈', '數(shù)學(xué)', 92),
(13, '陳哈哈', '英語(yǔ)', 80);然后，我們看一下咱們的測(cè)試表數(shù)據(jù)和預(yù)期查詢(xún)的結(jié)果。
#來(lái)源公眾號(hào)【碼農(nóng)編程進(jìn)階筆記】
mysql> SELECT * FROM t_gaokao_score;
+----+--------------+--------------+-------+
| id | student_name | subject | score |
+----+--------------+--------------+-------+
| 1 | 林磊兒 | 語(yǔ)文 | 148 |
| 2 | 林磊兒 | 數(shù)學(xué) | 150 |
| 3 | 林磊兒 | 英語(yǔ) | 147 |
| 4 | 喬英子 | 語(yǔ)文 | 121 |
| 5 | 喬英子 | 數(shù)學(xué) | 106 |
| 6 | 喬英子 | 英語(yǔ) | 146 |
| 7 | 方一凡 | 語(yǔ)文 | 70 |
| 8 | 方一凡 | 數(shù)學(xué) | 90 |
| 9 | 方一凡 | 英語(yǔ) | 59 |
| 10 | 方一凡 | 特長(zhǎng)加分 | 200 |
| 11 | 陳哈哈 | 語(yǔ)文 | 109 |
| 12 | 陳哈哈 | 數(shù)學(xué) | 92 |
| 13 | 陳哈哈 | 英語(yǔ) | 80 |
+----+--------------+--------------+-------+
13 rows in set (0.00 sec)
看看我們行轉(zhuǎn)列轉(zhuǎn)完后的結(jié)果:
一、行轉(zhuǎn)列SQL寫(xiě)法
方法一：使用case..when..then進(jìn)行 行轉(zhuǎn)列
#來(lái)源公眾號(hào)【碼農(nóng)編程進(jìn)階筆記】
SELECT student_name,
SUM(CASE `subject` WHEN '語(yǔ)文' THEN score ELSE 0 END) as '語(yǔ)文',
SUM(CASE `subject` WHEN '數(shù)學(xué)' THEN score ELSE 0 END) as '數(shù)學(xué)',
SUM(CASE `subject` WHEN '英語(yǔ)' THEN score ELSE 0 END) as '英語(yǔ)',
SUM(CASE `subject` WHEN '特長(zhǎng)加分' THEN score ELSE 0 END) as '特長(zhǎng)加分'
FROM t_gaokao_score
GROUP BY student_name;??這里如果不使用SUM()會(huì)報(bào)sql_mode=only_full_group_by相關(guān)錯(cuò)誤，需要聚合函數(shù)和group by連用或使用distinct才可以解決。
??其實(shí)，加了SUM()是為了能夠使用GROUP BY根據(jù)student_name進(jìn)行分組，每一個(gè)student_name對(duì)應(yīng)的subject="語(yǔ)文"的記錄畢竟只有一條，所以SUM() 的值就等于對(duì)應(yīng)那一條記錄的score的值。當(dāng)然，也可以換成MAX()。
方法二：使用IF()進(jìn)行 行轉(zhuǎn)列：
#來(lái)源公眾號(hào)【碼農(nóng)編程進(jìn)階筆記】
SELECT student_name,
SUM(IF(`subject`='語(yǔ)文',score,0)) as '語(yǔ)文',
SUM(IF(`subject`='數(shù)學(xué)',score,0)) as '數(shù)學(xué)',
SUM(IF(`subject`='英語(yǔ)',score,0)) as '英語(yǔ)',
SUM(IF(`subject`='特長(zhǎng)加分',score,0)) as '特長(zhǎng)加分'
FROM t_gaokao_score
GROUP BY student_name;二、如果領(lǐng)導(dǎo)@你，讓你在結(jié)果集中加上總數(shù)列呢？
#來(lái)源公眾號(hào)【碼農(nóng)編程進(jìn)階筆記】
SELECT IFNULL(student_name,'總數(shù)') AS student_name,
SUM(IF(`subject`='語(yǔ)文',score,0)) AS '語(yǔ)文',
SUM(IF(`subject`='數(shù)學(xué)',score,0)) AS '數(shù)學(xué)',
SUM(IF(`subject`='英語(yǔ)',score,0)) AS '英語(yǔ)',
SUM(IF(`subject`='特長(zhǎng)加分',score,0)) AS '特長(zhǎng)加分',
SUM(score) AS '總數(shù)'
FROM t_gaokao_score
GROUP BY student_name WITH ROLLUP;
三、領(lǐng)導(dǎo)又雙叒叕@你改需求
??這里我們就需要case when嵌套一下了，看著高大上，其實(shí)就是普通的嵌套而已。在第一層查出分組后的各科分?jǐn)?shù)，在第二層替換成等級(jí)即可。
SELECT student_name,
MAX(
CASE subject
WHEN '語(yǔ)文' THEN
(
CASE
WHEN score - (select avg(score) from t_gaokao_score where subject='語(yǔ)文') > 20 THEN
'優(yōu)秀'
WHEN score - (select avg(score) from t_gaokao_score where subject='語(yǔ)文') > 10 THEN
'良好'
WHEN score - (select avg(score) from t_gaokao_score where subject='語(yǔ)文') >= 0 THEN
'普通'
ELSE
'差'
END
)
END
) as '語(yǔ)文',
MAX(
CASE subject
WHEN '數(shù)學(xué)' THEN
(
CASE
WHEN score - (select avg(score) from t_gaokao_score where subject='數(shù)學(xué)') > 20 THEN
'優(yōu)秀'
WHEN score - (select avg(score) from t_gaokao_score where subject='數(shù)學(xué)') > 10 THEN
'良好'
WHEN score - (select avg(score) from t_gaokao_score where subject='數(shù)學(xué)') >= 0 THEN
'普通'
ELSE
'差'
END
)
END
) as '數(shù)學(xué)',
MAX(
CASE subject
WHEN '英語(yǔ)' THEN
(
CASE
WHEN score - (select avg(score) from t_gaokao_score where subject='英語(yǔ)') > 20 THEN
'優(yōu)秀'
WHEN score - (select avg(score) from t_gaokao_score where subject='英語(yǔ)') > 10 THEN
'良好'
WHEN score - (select avg(score) from t_gaokao_score where subject='英語(yǔ)') >= 0 THEN
'普通'
ELSE
'差'
END
)
END
) as '英語(yǔ)',
SUM(score) as '總分',
(CASE WHEN SUM(score) > 430 THEN '重點(diǎn)大學(xué)'
WHEN SUM(score) > 400 THEN '一本'
WHEN SUM(score) > 350 THEN '二本'
ELSE '工地搬磚'
END ) as '結(jié)果'
FROM t_gaokao_score
GROUP BY student_name
ORDER BY SUM(score) desc;我們來(lái)看一下輸出結(jié)果：
好了，SQL方面就是以上這些內(nèi)容了，有疑問(wèn)可以寫(xiě)在評(píng)論區(qū)留言！
評(píng)論
圖片
表情