列转行
运行前:
运行后:
源sql:
-- 创建学生表
CREATE TABLE test.stu_score
(
username VARCHAR(20), -- 学生姓名
subject VARCHAR(30), -- 科目
score FLOAT -- 成绩
);
-- 插入数据
use test;--mysql中需选中数据库
INSERT INTO stu_score SELECT '张三', '语文', 80;
INSERT INTO stu_score SELECT '张三', '数学', 90;
INSERT INTO stu_score SELECT '张三', '英语', 70;
INSERT INTO stu_score SELECT '张三', '生物', 85;
INSERT INTO stu_score SELECT '李四', '语文', 80;
INSERT INTO stu_score SELECT '李四', '数学', 92;
INSERT INTO stu_score SELECT '李四', '英语', 76;
INSERT INTO stu_score SELECT '李四', '生物', 88;
INSERT INTO stu_score SELECT '码农', '语文', 60;
INSERT INTO stu_score SELECT '码农', '数学', 82;
INSERT INTO stu_score SELECT '码农', '英语', 96;
INSERT INTO stu_score SELECT '码农', '生物', 78;
SELECT * from stu_score ;
-- 列转行
-- 这里用max函数和sum函数都可以
-- oracle、mysql都可用
SELECT username ,
MAX(CASE subject WHEN '数学' THEN score ELSE 0 END ) as 数学,
MAX(CASE subject WHEN '语文' THEN score ELSE 0 END ) as 语文,
MAX(CASE subject WHEN '英语' THEN score ELSE 0 END ) as 英语,
MAX(CASE subject WHEN '生物' THEN score ELSE 0 END ) as 生物
FROM stu_score
GROUP BY username;
或者
-- 适用于oracle中,mysql中没有decode函数
SELECT username ,
MAX(decode(subject,'数学',score,0)) as 数学,
MAX(decode(subject,'语文',score,0)) as 语文,
MAX(decode(subject,'英语',score,0)) as 英语,
MAX(decode(subject,'生物',score,0)) as 生物
FROM stu_score
GROUP BY username;
行转列
运行前:
运行后:
源sql:
CREATE TABLE test.stu_score2
(
username VARCHAR(20), -- 学生姓名
math int, -- 科目
chinese int,
english int,
biological int
);
DROP TABLE test.stu_score2;
-- 插入数据
use test;--mysql中需选中数据库
INSERT INTO stu_score2 SELECT '张三', 90, 80,70,85;
INSERT INTO stu_score2 SELECT '李四', 92, 80,76,88;
INSERT INTO stu_score2 SELECT '王五', 82, 60,96,78;
SELECT * FROM test.stu_score2;
-- 行转列
select username, '数学' COURSE , math as score from test.stu_score2
UNION
select username, '语文' COURSE, chinese as SCORE from test.stu_score2
UNION
select username, '英语' COURSE, english as SCORE from test.stu_score2
UNION
select username, '生物' COURSE, biological as SCORE from test.stu_score2
order by username,COURSE;