行轉(zhuǎn)列

題目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),
('張三', '數(shù)學(xué)', 88),
('張三', '英語', 98),
('李四', '語文', 89),
('李四', '數(shù)學(xué)', 76),
('李四', '英語', 90),
('王五', '語文', 99),
('王五', '數(shù)學(xué)', 66),
('王五', '英語', 91);

select name, sum(語文) as 語文, sum(數(shù)學(xué)) as 數(shù)學(xué), sum(英語) as 英語

from (select *,
             case students_scores.subject when "語文" then students_scores.score else 0 end as 語文,
             case students_scores.subject when "數(shù)學(xué)" then students_scores.score else 0 end as 數(shù)學(xué),
             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 "數(shù)學(xué)" then students_scores.score else 0 end) as 數(shù)學(xué),
       sum(case students_scores.subject when "英語" then students_scores.score else 0 end) as 英語

from students_scores
group by name

解題步驟

1. 確定分組列，轉(zhuǎn)換列，數(shù)據(jù)列
2. 生成偽列
3. 做分組查詢
4. 選擇合適的聚合函數(shù)

題目2

image.png

select results.ddate,
    count( case results.result when '勝' then 1 else null end )  as 勝,
       count(  case results.result when '負(fù)' then 1 else null end ) as 負(fù)
from results

group by  ddate

select results.ddate,
    sum( case results.result when '勝' then 1 else 0 end )  as 勝,
       sum(  case results.result when '負(fù)' then 1 else 0 end ) as 負(fù)
from results

group by  ddate

行轉(zhuǎn)列

image.png

select  name, '語文' as subject, 語文 as  score from  students_scores2
union  all
select  name, '數(shù)學(xué)' as subject, 數(shù)學(xué) as  score from  students_scores2
union  all
select  name, '英文' as subject, 英語 as  score from  students_scores2
order by  name