数据分析SQL面试题目9套-第3、4题（笔记）

题目来源：https://www.jianshu.com/p/0f165dcf9525

第3题

1.1 题目（行转列）

建表语句：

-- 创建 course 表
create table course (
id varchar(20),
teacher_id varchar(20),
week_day varchar(20),
has_course varchar(20)
);
insert into course value
(1,1,2,"Yes"),
(2,1,3,"Yes"),
(3,2,1,"Yes"),
(4,3,2,"Yes"),
(5,1,2,"Yes");

1.2 解题

select teacher_id,
       (case when week_day='1' then 'YES' else '' end) as 'mon',
       (case when week_day='2' then 'YES' else '' end) as 'tue',
       (case when week_day='3' then 'YES' else '' end) as 'thi',
       (case when week_day='4' then 'YES' else '' end) as 'thu',
       (case when week_day='5' then 'YES' else '' end) as 'fri'
from course;

第4题

2.1 题目（列转行）

建表语句：

create table a1 (
name varchar(20),
english int,
maths int,
music int);

insert into a1 values
("Jim",90,88,99);

2.2 解题

select name, 'english' as subject, english as score from a1
union all
select name, 'maths' as subject, maths as score from a1
union all
select name, 'music' as subject, music as score from a1
order by name;