# 改写优化SQL（4）：行转列标量子查询改为left join

（1）建表

--学生表
CREATE TABLE student
(
s# INT ,
sname NVARCHAR(32) ,
sage INT ,
ssex NVARCHAR(8)
)

--课程表
CREATE TABLE course
(
c# INT ,
cname NVARCHAR(32) ,
t# INT
)

--成绩表
CREATE TABLE sc ( s# INT, c# INT, score INT )

CREATE TABLE teacher
(
t# INT ,
tname NVARCHAR(16)
)

insert into Student select 1,N'刘一',18,N'男' union all
select 2,N'钱二',19,N'女' union all
select 3,N'张三',17,N'男' union all
select 4,N'李四',18,N'女' union all
select 5,N'王五',17,N'男' union all
select 6,N'赵六',19,N'女'

insert into Teacher
select 1,N'叶平' union all
select 2,N'贺高' union all
select 3,N'杨艳' union all
select 4,N'周磊'

insert into Course
select 1,N'语文',1 union all
select 2,N'数学',2 union all
select 3,N'英语',3 union all
select 4,N'物理',4

insert into SC
select 1,1,56 union all
select 1,2,78 union all
select 1,3,67 union all
select 1,4,58 union all
select 2,1,79 union all
select 2,2,81 union all
select 2,3,92 union all
select 2,4,68 union all
select 3,1,91 union all
select 3,2,47 union all
select 3,3,88 union all
select 3,4,56 union all
select 4,2,88 union all
select 4,3,90 union all
select 4,4,93 union all
select 5,1,46 union all
select 5,3,78 union all
select 5,4,53 union all
select 6,1,35 union all
select 6,2,68 union all
select 6,4,71

（2）标量子查询实现行转列

select  s.s#,
s.sname,
(select sc.score from sc,course c where sc.c# = c.c# and sc.s# = s.s# and c.cname='语文') as '语文',
(select sc.score from sc,course c where sc.c# = c.c# and sc.s# = s.s# and c.cname='数学') as '数学',
(select sc.score from sc,course c where sc.c# = c.c# and sc.s# = s.s# and c.cname='英语') as '英语',
(select sc.score from sc,course c where sc.c# = c.c# and sc.s# = s.s# and c.cname='物理') as '物理'
from student s
/*
s#	sname	语文	数学	英语	物理
1	刘一	56	78	67	58
2	钱二	79	81	92	68
3	张三	91	47	88	56
4	李四	NULL	88	90	93
5	王五	46	NULL	78	53
6	赵六	35	68	NULL	71
*/

（3）改写为left join

select  s.s#,
s.sname,
sc.v1 as '语文',
sc.v2 as '数学',
sc.v3 as '英语',
sc.v4 as '物理'
from student s
left join
(
select sc.s#,
max(case when c.cname = '语文' then sc.score end)  v1,
max(case when c.cname = '数学' then sc.score end)  v2,
max(case when c.cname = '英语' then sc.score end)  v3,
max(case when c.cname = '物理' then sc.score end)  v4
from sc,course c
where sc.c# = c.c#
group by sc.s#
)sc
on sc.s# = s.s#
/*
s#	sname	语文	数学	英语	物理
1	刘一	56	78	67	58
2	钱二	79	81	92	68
3	张三	91	47	88	56
4	李四	NULL	88	90	93
5	王五	46	NULL	78	53
6	赵六	35	68	NULL	71
*/