行转列

题目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

解题步骤

1. 确定分组列，转换列，数据列
2. 生成伪列
3. 做分组查询
4. 选择合适的聚合函数

题目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