凌河锦州网站建设,网站开发项目经理注意事项,甘肃建设住房厅网站,信阳住房和城乡建设局网站行转列方法总结1、使用case…when…then2、使用SUM(IF()) 生成列3、使用SUM(IF()) 生成列 WITH ROLLUP 生成汇总行4、使用SUM(IF()) 生成列 UNION 生成汇总行,并利用 IFNULL将汇总行标题显示为 Total5、使用SUM(IF()) 生成列#xff0c;直接生成汇总结果#xff0c;不再利用…行转列方法总结1、使用case…when…then2、使用SUM(IF()) 生成列3、使用SUM(IF()) 生成列 WITH ROLLUP 生成汇总行4、使用SUM(IF()) 生成列 UNION 生成汇总行,并利用 IFNULL将汇总行标题显示为 Total5、使用SUM(IF()) 生成列直接生成汇总结果不再利用子查询环境准备建表语句 初始化数据-- house.base_score definitionCREATE TABLE base_score (id int NOT NULL AUTO_INCREMENT,userid varchar(20) NOT NULL COMMENT 用户id,subject varchar(20) DEFAULT NULL COMMENT 科目,score double DEFAULT NULL COMMENT 成绩,PRIMARY KEY (id)
) ENGINEInnoDB AUTO_INCREMENT11 DEFAULT CHARSETutf8;--- 插入数据INSERT INTO base_score(userid,subject,score) VALUES (001,语文,90);
INSERT INTO base_score(userid,subject,score) VALUES (001,数学,92);
INSERT INTO base_score(userid,subject,score) VALUES (001,英语,80);
INSERT INTO base_score(userid,subject,score) VALUES (002,语文,88);
INSERT INTO base_score(userid,subject,score) VALUES (002,数学,90);
INSERT INTO base_score(userid,subject,score) VALUES (002,英语,75.5);
INSERT INTO base_score(userid,subject,score) VALUES (003,语文,70);
INSERT INTO base_score(userid,subject,score) VALUES (003,数学,85);
INSERT INTO base_score(userid,subject,score) VALUES (003,英语,90);
INSERT INTO base_score(userid,subject,score) VALUES (003,政治,82);使用case…when…then 实现行转列-- 通过case...when...then 实现行记录转列记录
SELECT userid,
SUM(CASE subject WHEN 语文 THEN score ELSE 0 END) as 语文,
SUM(CASE subject WHEN 数学 THEN score ELSE 0 END) as 数学,
SUM(CASE subject WHEN 英语 THEN score ELSE 0 END) as 英语,
SUM(CASE subject WHEN 政治 THEN score ELSE 0 END) as 政治
FROM base_score
GROUP BY userid使用SUM(IF()) 生成行转列-- 使用sum(if) 实现行记录转列记录
SELECT userid,
SUM(IF(subject语文,score,0)) as 语文,
SUM(IF(subject数学,score,0)) as 数学,
SUM(IF(subject英语,score,0)) as 英语,
SUM(IF(subject政治,score,0)) as 政治
FROM base_score
GROUP BY userid温馨提示1SUM() 是为了能够使用GROUP BY根据userid进行分组因为每一个userid对应的subject语文的记录只有一条所以SUM() 的值就等于对应那一条记录的score的值。假如userid ‘001’ and subject‘语文’ 的记录有两条则此时SUM() 的值将会是这两条记录的和同理使用Max()的值将会是这两条记录里面值最大的一个。但是正常情况下一个user对应一个subject只有一个分数因此可以使用SUM()、MAX()、MIN()、AVG()等聚合函数都可以达到行转列的效果。2IF(subject‘语文’,score,0) 作为条件即对所有subject语文’的记录的score字段进行SUM()、MAX()、MIN()、AVG()操作如果score没有值则默认为0。使用SUM(IF()) 生成列 WITH ROLLUP 生成汇总行-- 使用rollup 生成汇总记录SELECT userid,IFNULL(subject,total) AS subject,SUM(score) AS scoreFROM base_scoreGROUP BY userid,subjectWITH ROLLUPHAVING userid IS NOT NULL-- 使用sum(if) 实现行记录转列记录 WITH ROLLUP 生成汇总行SELECT IFNULL(userid,total) AS userid,
SUM(IF(subject语文,score,0)) as 语文,
SUM(IF(subject数学,score,0)) as 数学,
SUM(IF(subject英语,score,0)) as 英语,
SUM(IF(subject政治,score,0)) as 政治,
SUM(IF(subjecttotal,score,0)) AS 总计
FROM (SELECT userid,IFNULL(subject,total) AS subject,SUM(score) AS scoreFROM base_scoreGROUP BY userid,subjectWITH ROLLUPHAVING userid IS NOT NULL
) as tmp
GROUP BY userid
WITH ROLLUP;使用SUM(IF()) 生成列 UNION 生成汇总行,并利用 IFNULL将汇总行标题显示为 Total-- 利用SUM(IF()) 生成列 UNION 生成汇总行,并利用 IFNULL将汇总行标题显示为 Total
SELECT userid,
SUM(IF(subject语文,score,0)) AS 语文,
SUM(IF(subject数学,score,0)) AS 数学,
SUM(IF(subject英语,score,0)) AS 英语,
SUM(IF(subject政治,score,0)) AS 政治,
SUM(score) AS TOTAL
FROM base_score
GROUP BY userid
UNION
SELECT TOTAL,
SUM(IF(subject语文,score,0)) AS 语文,
SUM(IF(subject数学,score,0)) AS 数学,
SUM(IF(subject英语,score,0)) AS 英语,
SUM(IF(subject政治,score,0)) AS 政治,
SUM(score) FROM base_score利用SUM(IF()) 生成列直接生成结果不再利用子查询-- 利用SUM(IF()) 生成列 with rollup 汇总列直接生成结果不再利用子查询
SELECT userid,
SUM(IF(subject语文,score,0)) AS 语文,
SUM(IF(subject数学,score,0)) AS 数学,
SUM(IF(subject英语,score,0)) AS 英语,
SUM(IF(subject政治,score,0)) AS 政治,
SUM(score) AS TOTAL
FROM base_score
GROUP BY userid WITH ROLLUP;列转行方法总结环境准备建表语句 初始化数据CREATE TABLE base_score1(id INT(11) NOT NULL auto_increment,userid VARCHAR(20) NOT NULL COMMENT 用户id,cn_score DOUBLE COMMENT 语文成绩,math_score DOUBLE COMMENT 数学成绩,en_score DOUBLE COMMENT 英语成绩,po_score DOUBLE COMMENT 政治成绩,PRIMARY KEY(id)
)ENGINE INNODB DEFAULT CHARSET utf8;INSERT INTO base_score1(userid,cn_score,math_score,en_score,po_score) VALUES (001,90,92,80,0);
INSERT INTO base_score1(userid,cn_score,math_score,en_score,po_score) VALUES (002,88,90,75.5,0);
INSERT INTO base_score1(userid,cn_score,math_score,en_score,po_score) VALUES (003,70,85,90,82);select * from base_score1SELECT userid,语文 AS course,cn_score AS score FROM base_score1
UNION ALL
SELECT userid,数学 AS course,math_score AS score FROM base_score1
UNION ALL
SELECT userid,英语 AS course,en_score AS score FROM base_score1
UNION ALL
SELECT userid,政治 AS course,po_score AS score FROM base_score1拓展UNION与UNION ALL的区别对重复结果的处理UNION会去掉重复记录UNION ALL不会对排序的处理UNION会排序UNION ALL只是简单地将两个结果集合并效率方面的区别因为UNION 会做去重和排序处理因此效率比UNION ALL慢很多
