SQL行轉(zhuǎn)列-列轉(zhuǎn)行
一、行轉(zhuǎn)列
建表語(yǔ)句
DROP TABLE IF EXISTS tb_score;
CREATE TABLE tb_score(
id INT(11) NOT NULL auto_increment,
userid VARCHAR(20) NOT NULL COMMENT '用戶id',
subject VARCHAR(20) COMMENT '科目',
score DOUBLE COMMENT '成績(jī)',
PRIMARY KEY(id)
)ENGINE = INNODB DEFAULT CHARSET = utf8;
插入數(shù)據(jù)
INSERT INTO tb_score(userid,subject,score) VALUES ('001','語(yǔ)文',90);
INSERT INTO tb_score(userid,subject,score) VALUES ('001','數(shù)學(xué)',92);
INSERT INTO tb_score(userid,subject,score) VALUES ('001','英語(yǔ)',80);
INSERT INTO tb_score(userid,subject,score) VALUES ('002','語(yǔ)文',88);
INSERT INTO tb_score(userid,subject,score) VALUES ('002','數(shù)學(xué)',90);
INSERT INTO tb_score(userid,subject,score) VALUES ('002','英語(yǔ)',75.5);
INSERT INTO tb_score(userid,subject,score) VALUES ('003','語(yǔ)文',70);
INSERT INTO tb_score(userid,subject,score) VALUES ('003','數(shù)學(xué)',85);
INSERT INTO tb_score(userid,subject,score) VALUES ('003','英語(yǔ)',90);
INSERT INTO tb_score(userid,subject,score) VALUES ('003','政治',82);
查詢數(shù)據(jù)表中的內(nèi)容（即轉(zhuǎn)換前的結(jié)果）
SELECT * FROM tb_score
先來(lái)看一下轉(zhuǎn)換后的結(jié)果：
1. 使用case...when....then 進(jìn)行行轉(zhuǎn)列
SELECT userid,
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 '政治' THEN score ELSE 0 END) as '政治'
FROM tb_score
GROUP BY userid
2. 使用IF() 進(jìn)行行轉(zhuǎn)列
SELECT userid,
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`='政治',score,0)) as '政治'
FROM tb_score
GROUP BY userid
注意點(diǎn)：
（1）SUM() 是為了能夠使用GROUP BY根據(jù)userid進(jìn)行分組诺凡，因?yàn)槊恳粋€(gè)userid對(duì)應(yīng)的subject="語(yǔ)文"的記錄只有一條，所以SUM() 的值就等于對(duì)應(yīng)那一條記錄的score的值乌叶。
假如userid ='001' and subject='語(yǔ)文' 的記錄有兩條，則此時(shí)SUM() 的值將會(huì)是這兩條記錄的和听哭，同理痊远，使用Max()的值將會(huì)是這兩條記錄里面值最大的一個(gè)肩榕。但是正常情況下刚陡，一個(gè)user對(duì)應(yīng)一個(gè)subject只有一個(gè)分?jǐn)?shù)，因此可以使用SUM()株汉、MAX()筐乳、MIN()、AVG()等聚合函數(shù)都可以達(dá)到行轉(zhuǎn)列的效果乔妈。
（2）IF(subject='語(yǔ)文',score,0) 作為條件蝙云，即對(duì)所有subject='語(yǔ)文'的記錄的score字段進(jìn)行SUM()、MAX()路召、MIN()勃刨、AVG()操作，如果score沒(méi)有值則默認(rèn)為0股淡。
3. 利用SUM(IF()) 生成列 + WITH ROLLUP 生成匯總行,并利用 IFNULL將匯總行標(biāo)題顯示為Total
SELECT IFNULL(userid,'total') AS userid,
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`='政治',score,0)) AS 政治,
SUM(IF(`subject`='total',score,0)) AS total
FROM(
SELECT userid,IFNULL(`subject`,'total') AS `subject`,SUM(score) AS score
FROM tb_score
GROUP BY userid,`subject`
WITH ROLLUP
HAVING userid IS NOT NULL
)AS A
GROUP BY userid
WITH ROLLUP;
運(yùn)行結(jié)果：
4. 利用SUM(IF()) 生成列 + UNION 生成匯總行,并利用 IFNULL將匯總行標(biāo)題顯示為 Total
SELECT userid,
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`='政治',score,0)) AS 政治,
SUM(score) AS TOTAL
FROM tb_score
GROUP BY userid
UNION
SELECT 'TOTAL',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`='政治',score,0)) AS 政治,
SUM(score) FROM tb_score
運(yùn)行結(jié)果：
5. 利用SUM(IF()) 生成列朵你，直接生成結(jié)果不再利用子查詢
SELECT IFNULL(userid,'TOTAL') AS userid,
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`='政治',score,0)) AS 政治,
SUM(score) AS TOTAL
FROM tb_score
GROUP BY userid WITH ROLLUP;
運(yùn)行結(jié)果：
6、動(dòng)態(tài)揣非，適用于列不確定情況
SET @EE='';
select @EE :=CONCAT(@EE,'sum(if(subject= \'',subject,'\',score,0)) as ',subject, ',') AS aa FROM (SELECT DISTINCT subject FROM tb_score) A ;
SET @QQ = CONCAT('select ifnull(userid,\'TOTAL\')as userid,',@EE,' sum(score) as TOTAL from tb_score group by userid WITH ROLLUP');
-- SELECT @QQ;
PREPARE stmt FROM @QQ;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
運(yùn)行結(jié)果：
7. 合并字段顯示：利用group_concat()
SELECT userid,GROUP_CONCAT(`subject`,":",score)AS 成績(jī) FROM tb_score
GROUP BY userid
運(yùn)行結(jié)果：
group_concat()，手冊(cè)上說(shuō)明:該函數(shù)返回帶有來(lái)自一個(gè)組的連接的非NULL值的字符串結(jié)果躲因。比較抽象早敬，難以理解。通俗點(diǎn)理解大脉，其實(shí)是這樣的：group_concat()會(huì)計(jì)算哪些行屬于同一組搞监，將屬于同一組的列顯示出來(lái)。要返回哪些列镰矿，由函數(shù)參數(shù)(就是字段名)決定琐驴。分組必須有個(gè)標(biāo)準(zhǔn)，就是根據(jù)group by指定的列進(jìn)行分組。
結(jié)論：group_concat()函數(shù)可以很好的建屬于同一分組的多個(gè)行轉(zhuǎn)化為一個(gè)列绝淡。
三宙刘、列轉(zhuǎn)行
建表語(yǔ)句：
CREATE TABLE tb_score1(
id INT(11) NOT NULL auto_increment,
userid VARCHAR(20) NOT NULL COMMENT '用戶id',
cn_score DOUBLE COMMENT '語(yǔ)文成績(jī)',
math_score DOUBLE COMMENT '數(shù)學(xué)成績(jī)',
en_score DOUBLE COMMENT '英語(yǔ)成績(jī)',
po_score DOUBLE COMMENT '政治成績(jī)',
PRIMARY KEY(id)
)ENGINE = INNODB DEFAULT CHARSET = utf8;
插入數(shù)據(jù)：
INSERT INTO tb_score1(userid,cn_score,math_score,en_score,po_score) VALUES ('001',90,92,80,0);
INSERT INTO tb_score1(userid,cn_score,math_score,en_score,po_score) VALUES ('002',88,90,75.5,0);
INSERT INTO tb_score1(userid,cn_score,math_score,en_score,po_score) VALUES ('003',70,85,90,82);
查詢數(shù)據(jù)表中的內(nèi)容（即轉(zhuǎn)換前的結(jié)果）
SELECT * FROM tb_score1
轉(zhuǎn)換后：
本質(zhì)是將userid的每個(gè)科目分?jǐn)?shù)分散成一條記錄顯示出來(lái)。
直接上SQL：
SELECT userid,'語(yǔ)文' AS course,cn_score AS score FROM tb_score1
UNION ALL
SELECT userid,'數(shù)學(xué)' AS course,math_score AS score FROM tb_score1
UNION ALL
SELECT userid,'英語(yǔ)' AS course,en_score AS score FROM tb_score1
UNION ALL
SELECT userid,'政治' AS course,po_score AS score FROM tb_score1
ORDER BY userid
這里將每個(gè)userid對(duì)應(yīng)的多個(gè)科目的成績(jī)查出來(lái)牢酵，通過(guò)UNION ALL將結(jié)果集加起來(lái)悬包，達(dá)到上圖的效果。
附：UNION與UNION ALL的區(qū)別（摘）：
1.對(duì)重復(fù)結(jié)果的處理：UNION會(huì)去掉重復(fù)記錄馍乙，UNION ALL不會(huì)布近；
2.對(duì)排序的處理：UNION會(huì)排序，UNION ALL只是簡(jiǎn)單地將兩個(gè)結(jié)果集合并丝格；
3.效率方面的區(qū)別：因?yàn)閁NION 會(huì)做去重和排序處理撑瞧，因此效率比UNION ALL慢很多；
