1 摘要
mysql 行列轉(zhuǎn)換 ，在項(xiàng)目中應(yīng)用的極其頻繁，尤其是一些金融項(xiàng)目里的報(bào)表。其中最為頭痛的就是多行轉(zhuǎn)多列，動(dòng)態(tài)的列行轉(zhuǎn)換。
2 實(shí)踐
2.1 創(chuàng)建表
-- 創(chuàng)建表 學(xué)生表
CREATE TABLE `student` (
`stuid` VARCHAR(16) NOT NULL COMMENT '學(xué)號',
`stunm` VARCHAR(20) NOT NULL COMMENT '學(xué)生姓名',
PRIMARY KEY (`stuid`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;
-- 課程表
CREATE TABLE `courses` (
`courseno` VARCHAR(20) NOT NULL,
`coursenm` VARCHAR(100) NOT NULL,
PRIMARY KEY (`courseno`)
)
COMMENT='課程表'
COLLATE='utf8_general_ci'
ENGINE=InnoDB;
-- 成績表
CREATE TABLE `score` (
`stuid` VARCHAR(16) NOT NULL,
`courseno` VARCHAR(20) NOT NULL,
`scores` FLOAT NULL DEFAULT NULL,
PRIMARY KEY (`stuid`, `courseno`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;
2.2 插入數(shù)據(jù)
-- 學(xué)生表數(shù)據(jù)
Insert Into student (stuid, stunm) Values('1001', '張三');
Insert Into student (stuid, stunm) Values('1002', '李四');
Insert Into student (stuid, stunm) Values('1003', '趙二');
Insert Into student (stuid, stunm) Values('1004', '王五');
Insert Into student (stuid, stunm) Values('1005', '劉青');
Insert Into student (stuid, stunm) Values('1006', '周明');
-- 課程表數(shù)據(jù)
Insert Into courses (courseno, coursenm) Values('C001', '大學(xué)語文');
Insert Into courses (courseno, coursenm) Values('C002', '新視野英語');
Insert Into courses (courseno, coursenm) Values('C003', '離散數(shù)學(xué)');
Insert Into courses (courseno, coursenm) Values('C004', '概率論與數(shù)理統(tǒng)計(jì)');
Insert Into courses (courseno, coursenm) Values('C005', '線性代數(shù)');
Insert Into courses (courseno, coursenm) Values('C006', '高等數(shù)學(xué)(一)');
Insert Into courses (courseno, coursenm) Values('C007', '高等數(shù)學(xué)(二)');
-- 成績表數(shù)據(jù)
Insert Into score(stuid, courseno, scores) Values('1001', 'C001', 67);
Insert Into score(stuid, courseno, scores) Values('1002', 'C001', 68);
Insert Into score(stuid, courseno, scores) Values('1003', 'C001', 69);
Insert Into score(stuid, courseno, scores) Values('1004', 'C001', 70);
Insert Into score(stuid, courseno, scores) Values('1005', 'C001', 71);
Insert Into score(stuid, courseno, scores) Values('1006', 'C001', 72);
Insert Into score(stuid, courseno, scores) Values('1001', 'C002', 87);
Insert Into score(stuid, courseno, scores) Values('1002', 'C002', 88);
Insert Into score(stuid, courseno, scores) Values('1003', 'C002', 89);
Insert Into score(stuid, courseno, scores) Values('1004', 'C002', 90);
Insert Into score(stuid, courseno, scores) Values('1005', 'C002', 91);
Insert Into score(stuid, courseno, scores) Values('1006', 'C002', 92);
2.3 要求
- 查詢每個(gè)學(xué)生的每門課程每門成績
方法1
- 多表連接
select
st.stuid ID , st.stunm 姓名, cs.coursenm 課程名 ,sc.scores 成績
from student st, score sc ,courses cs
where st.stuid = sc.stuid and sc.courseno = cs.courseno
-
結(jié)果
方法2
- 靜態(tài)行專列：ID對應(yīng)姓名對應(yīng)每門課程對應(yīng)每門成績
select st.stuid 編號, st.stunm 姓名 ,
Max(case c.coursenm when '大學(xué)語文' then s.scores else 0 end ) '大學(xué)語文',
max(case c.coursenm when '新視野英語' then IFNULL(s.scores,0)else 0 end) '新視野英語',
Max(case c.coursenm when '離散數(shù)學(xué)' then IFNULL(s.scores,0) ELSE 0 END) '離散數(shù)學(xué)',
MAX(case c.coursenm when '概率論與數(shù)理統(tǒng)計(jì)' then IFNULL(s.scores,0) else 0 end) '概率論與數(shù)理統(tǒng)計(jì)',
MAX(case c.coursenm when '線性代數(shù)' then IFNULL(s.scores,0) else 0 END) '線性代數(shù)',
MAX(case c.coursenm when '高等數(shù)學(xué)(一)' THEN IFNULL(s.scores,0) else 0 end) '高等數(shù)學(xué)(一)',
MAX(case c.coursenm when '高等數(shù)學(xué)(二)' THEN IFNULL(s.scores,0) else 0 end) '高等數(shù)學(xué)(二)'
from student st
LEFT JOIN score s on st.stuid = s.stuid
LEFT JOIN courses c on c.courseno = s.courseno
GROUP BY st.stuid
# IFNULL(expr1,expr2) 如果 expr1 不是 NULL，IFNULL() 返回 expr1，否則它返回 expr2
-
結(jié)果
方法3
- group_concat() 函數(shù)
- 該函數(shù)返回帶有來自一個(gè)組的連接的非NULL值的字符串結(jié)果；
- group_concat()會(huì)計(jì)算哪些行屬于同一組，將屬于同一組的列顯示出來。
要返回哪些列，由函數(shù)參數(shù)(就是字段名)決定。
分組必須有個(gè)標(biāo)準(zhǔn)，就是根據(jù)group by指定的列進(jìn)行分組。
select
s.stuid 編號 , GROUP_CONCAT(courseno) 課程號 , GROUP_CONCAT(s.scores) 成績
from score s GROUP BY s.stuid
-
結(jié)果
