行转列
题目1
image.png
CREATE TABLE students_scores (
id INT AUTO_INCREMENT,
name VARCHAR(50),
subject VARCHAR(50),
score INT,
PRIMARY KEY(id)
);
INSERT INTO students_scores (name, subject, score) VALUES
('张三', '语文', 78),
('张三', '数学', 88),
('张三', '英语', 98),
('李四', '语文', 89),
('李四', '数学', 76),
('李四', '英语', 90),
('王五', '语文', 99),
('王五', '数学', 66),
('王五', '英语', 91);
select name, sum(语文) as 语文, sum(数学) as 数学, sum(英语) as 英语
from (select *,
case students_scores.subject when "语文" then students_scores.score else 0 end as 语文,
case students_scores.subject when "数学" then students_scores.score else 0 end as 数学,
case students_scores.subject when "英语" then students_scores.score else 0 end as 英语
from students_scores) as ss
group by name;
select name,
sum(case students_scores.subject when "语文" then students_scores.score else 0 end) as 语文,
sum(case students_scores.subject when "数学" then students_scores.score else 0 end) as 数学,
sum(case students_scores.subject when "英语" then students_scores.score else 0 end) as 英语
from students_scores
group by name
解题步骤
- 确定分组列，转换列，数据列
- 生成伪列
- 做分组查询
- 选择合适的聚合函数
题目2
image.png
select results.ddate,
count( case results.result when '胜' then 1 else null end ) as 胜,
count( case results.result when '负' then 1 else null end ) as 负
from results
group by ddate
select results.ddate,
sum( case results.result when '胜' then 1 else 0 end ) as 胜,
sum( case results.result when '负' then 1 else 0 end ) as 负
from results
group by ddate
行转列
image.png
select name, '语文' as subject, 语文 as score from students_scores2
union all
select name, '数学' as subject, 数学 as score from students_scores2
union all
select name, '英文' as subject, 英语 as score from students_scores2
order by name
