mysql中列转行

参考：https://www.cnblogs.com/mjbenkyo/p/7268242.html

创建表

CREATE TABLE grade (
studyCode varchar(20) NOT NULL DEFAULT ‘’ COMMENT ‘学号’,
subjectS varchar(20) NOT NULL,
score int(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO grade VALUES (‘001’, ‘数学’, ‘120’);
INSERT INTO grade VALUES (‘002’, ‘数学’, ‘130’);
INSERT INTO grade VALUES (‘003’, ‘数学’, ‘125’);
INSERT INTO grade VALUES (‘001’, ‘英语’, ‘130’);
INSERT INTO grade VALUES (‘002’, ‘英语’, ‘140’);
INSERT INTO grade VALUES (‘003’, ‘英语’, ‘135’);
INSERT INTO grade VALUES (‘001’, ‘国学’, ‘110’);
INSERT INTO grade VALUES (‘002’, ‘国学’, ‘136’);
INSERT INTO grade VALUES (‘003’, ‘国学’, ‘145’);

1. 列转行方式一：

select studyCode 学号,
SUM(IF(subjectS = ‘国学’,score,0)) 国学,
SUM(IF(subjectS = ‘数学’,score,0)) 数学,
SUM(IF(subjectS = ‘英语’,score,0)) 英语
FROM grade
GROUP BY studyCode;

2. 列转行方式二：

select studyCode ,
sum( case when subjects = '国学’then score end ) 国学,
sum( case when subjects =“数学” then score end ) 数学,
sum( case when subjects = ‘英语’ then score end ) 英语
from grade GROUP by studyCode ;

3. 列转行，增加统计列，方式一：

• with rollup 统计 sum函数标记列
• IFNULL(columnName,value) 如果column值为null，使用value替换,适用于一个参数值为null
• coalesce (columnName1,columnName1,value1,value2),如果列值都为null，选择最后一个value2,适合多个参数值为null

select COALESCE(studyCode,‘总计’) 学号,
sum(if(subjects=‘国学’,score,0)) 国学,
sum(if(subjects=‘数学’,score,0)) 数学,
sum(if(subjects=‘英语’,score,0)) 英语,
sum(score) 总计
from grade group by studyCode
with rollup ;

4. 列转行，增加统计，方式二：

select studyCode,
sum(if(subjects=‘国学’,score,0)) 国学,
sum(if(subjects=‘数学’,score,0)) 数学,
sum(if(subjects=‘英语’,score,0)) 英语,
sum(score) 总计
from grade
group by studyCode
union
select ‘总计’ studyCode ,
sum(if(subjects=‘国学’,score,0)) 国学,
sum(if(subjects=‘数学’,score,0)) 数学,
sum(if(subjects=‘英语’,score,0)) 英语,
sum(score) 总计
from grade

其他

SELECT
IFNULL(NULL, 1) as ifnull,
COALESCE (NULL, 1) as coalesceTwo,
COALESCE (NULL, null, null,null, 2) as coalesceFour,
COALESCE (NULL, NULL, NULL, NULL) as coalesceAllNull;

select if(2=2,3) from dual