mysql中的列转行操作示例和带统计的列转行统计
mysql中的列转行操作示例和带统计的列转行统计
首先建表并导入数据：
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’);
数据如下：
SELECT * FROM grade ;
纯列转行：方法1 sum(if()) ,这里的if()做增加列操作
SELECT
studyCode 学号,
SUM(IF(subjectS = ‘国学’,score,0)) 国学,
SUM(IF(subjectS = ‘数学’,score,0)) 数学,
SUM(IF(subjectS = ‘英语’,score,0)) 英语
FROM rowandcol
GROUP BY studyCode;
结果如下：
方法2.使用CASE WHEN THEN ELSE END也可以
SELECT
studyCode 学号,
SUM(CASE WHEN subjectS = ‘国学’ THEN score ELSE 0 END) 国学,
SUM(CASE WHEN subjectS = ‘数学’ THEN score ELSE 0 END) 数学,
SUM(CASE WHEN subjectS = ‘英语’ THEN score ELSE 0 END) 英语
FROM grade
GROUP BY studyCode;
结果如下：
二 带统计的列转行
SELECT studyCode,IFNULL(subjectS,‘total’),SUM(score) FROM grade GROUP BY studyCode,subjectS WITH ROLLUP ;
【其中，with rollup 在group分组字段的基础上再进行统计数据】
结果如下：