一、MySQL版本
二、行转列
1.行转列 方法一
1.1 建表语句
CREATE TABLE ` student` (
` id` int ( 7 ) NOT NULL COMMENT '学号' ,
` stu_name` varchar ( 20 ) NOT NULL COMMENT '学生姓名' ,
` course_name` varchar ( 100 ) NOT NULL COMMENT '课程名称' ,
` score` float DEFAULT NULL COMMENT '分数' ,
PRIMARY KEY ( ` id` )
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci;
insert into ` student` ( ` id` , ` stu_name` , ` course_name` , ` score` ) values
( 1 , '张三' , '数学' , 67 ) ,
( 2 , '张三' , '语文' , 88 ) ,
( 3 , '张三' , '英语' , 91 ) ,
( 4 , '李四' , '数学' , 73 ) ,
( 5 , '李四' , '语文' , 79 ) ,
( 6 , '李四' , '英语' , 82 ) ,
( 7 , '王五' , '数学' , 95 ) ,
( 8 , '王五' , '语文' , 91 ) ,
( 9 , '王五' , '英语' , 63 ) ,
( 10 , '赵六' , '数学' , 84 ) ,
( 11 , '赵六' , '语文' , 89 ) ,
( 12 , '赵六' , '英语' , 80 ) ,
( 13 , '田七' , '数学' , 77 ) ,
( 14 , '田七' , '语文' , 81 ) ,
( 15 , '田七' , '英语' , 92 ) ,
( 16 , '周八' , '数学' , 50 ) ,
( 17 , '周八' , '语文' , 42 ) ,
( 18 , '周八' , '英语' , 62 ) ;
1.2 数据展示
1.3 编写SQL
SELECT
stu_name ,
MAX ( CASE course_name WHEN '数学' THEN score ELSE NULL END ) '数学1' ,
MAX ( CASE course_name WHEN '语文' THEN score ELSE NULL END ) '语文1' ,
MAX ( CASE course_name WHEN '英语' THEN score ELSE NULL END ) '英语1'
FROM
student
GROUP BY
stu_name ;
1.4 执行结果
1.5 计算个人总分数和每门课程平均分数
1.5.1 WITH ROLLUP + sum() 方法一
先查询出个人总分数作为子表,然后行转列操作计算每门课程的平均分 编写SQL
SELECT
IFNULL( stu_name, '平均分(方式一)' ) stu_name,
FORMAT ( AVG ( CASE course_name WHEN '数学' THEN score ELSE NULL END ) , 2 ) '数学1' ,
FORMAT ( AVG ( CASE course_name WHEN '语文' THEN score ELSE NULL END ) , 2 ) '语文1' ,
FORMAT ( AVG ( CASE course_name WHEN '英语' THEN score ELSE NULL END ) , 2 ) '英语1' ,
SUM ( CASE course_name WHEN 'selfTotal' THEN score ELSE NULL END ) 'selfTotal'
FROM
(
SELECT
stu_name,
IFNULL( course_name, 'selfTotal' ) course_name,
SUM ( score ) score
FROM
student
GROUP BY
stu_name,
course_name WITH ROLLUP
HAVING
stu_name IS NOT NULL
) t1
GROUP BY
stu_name WITH ROLLUP
执行结果:
1.5.2 UNION + sum() 方法二
SELECT
IFNULL( stu_name, '平均分(方式二)' ) stu_name,
SUM ( CASE course_name WHEN '数学' THEN score ELSE NULL END ) '数学1' ,
SUM ( CASE course_name WHEN '语文' THEN score ELSE NULL END ) '语文1' ,
SUM ( CASE course_name WHEN '英语' THEN score ELSE NULL END ) '英语1' ,
SUM ( score ) 'selfTotal'
FROM
student
GROUP BY
stu_name UNION
SELECT
'平均分(方式二)' ,
FORMAT ( AVG ( CASE course_name WHEN '数学' THEN score ELSE NULL END ) , 2 ) '数学1' ,
FORMAT ( AVG ( CASE course_name WHEN '语文' THEN score ELSE NULL END ) , 2 ) '语文1' ,
FORMAT ( AVG ( CASE course_name WHEN '英语' THEN score ELSE NULL END ) , 2 ) '英语1' ,
SUM ( score ) 'selfTotal'
FROM
student
执行结果
1.5.2 直接使用sum(),不再使用子查询 方法三
SELECT
IFNULL( stu_name, '平均分(方式三)' ) AS stu_name,
FORMAT ( AVG ( CASE course_name WHEN '数学' THEN score ELSE NULL END ) , 2 ) '数学1' ,
FORMAT ( AVG ( CASE course_name WHEN '语文' THEN score ELSE NULL END ) , 2 ) '语文1' ,
FORMAT ( AVG ( CASE course_name WHEN '英语' THEN score ELSE NULL END ) , 2 ) '英语1' ,
SUM ( score ) AS selfTotal
FROM
student
GROUP BY
stu_name WITH ROLLUP ;
执行结果
2.行转列 方法二
2.1 编写SQL
SELECT DISTINCT
t2. stu_name,
( SELECT score FROM student t1 WHERE t1. stu_name = t2. stu_name AND t1. course_name = '数学' ) AS '数学2' ,
( SELECT score FROM student t1 WHERE t1. stu_name = t2. stu_name AND t1. course_name = '语文' ) AS '语文2' ,
( SELECT score FROM student t1 WHERE t1. stu_name = t2. stu_name AND t1. course_name = '英语' ) AS '英语2'
FROM
student t2;
2.2 执行结果
3.行转列 方法三
不再使用 case…when…then…else…end语法,直接使用if()
3.1 编写SQL
SELECT
stu_name,
SUM ( IF ( course_name = '数学' , score, 0 ) ) AS '语文3' ,
SUM ( IF ( course_name = '语文' , score, 0 ) ) AS '数学3' ,
SUM ( IF ( course_name = '英语' , score, 0 ) ) AS '英语3'
FROM
student
GROUP BY
stu_name
3.2执行结果
三、列转行
3.1 建表语句
CREATE TABLE ` student1` (
` id` int ( 5 ) NOT NULL AUTO_INCREMENT COMMENT '主键' ,
` stu_name` varchar ( 32 ) DEFAULT NULL COMMENT '学生名称' ,
` language ` varchar ( 32 ) DEFAULT NULL COMMENT '语文' ,
` math` varchar ( 32 ) DEFAULT NULL COMMENT '数学' ,
` english` varchar ( 32 ) DEFAULT NULL COMMENT '英语' ,
PRIMARY KEY ( ` id` )
) ENGINE = InnoDB AUTO_INCREMENT = 6 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci;
insert into ` student1` ( ` id` , ` stu_name` , ` language ` , ` math` , ` english` ) values
( 1 , '张三' , '67' , '88' , '91' ) ,
( 2 , '李四' , '73' , '79' , '82' ) ,
( 3 , '王五' , '95' , '91' , '63'
3.2 数据展示
3.3 编写SQL
SELECT stu_name, '语文' AS course_name, LANGUAGE AS score FROM student1
UNION ALL
SELECT stu_name, '数学' AS course_name, math AS score FROM student1
UNION ALL
SELECT stu_name, '英语' AS course_name, english AS score FROM student1
ORDER BY stu_name
3.4 执行结果
执行结果: