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

前面两篇文章写的是，如何把 标量子查询、聚合标量子查询，转为 left join，标量子查询还可以实现行转列，但是同样的，效率比较差，建议转成 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
 */

执行计划：

比较一下两种写法的差异，发现标量子查询的写法，对成绩表、课程表，分别访问了4次，共8次，而left join的写法，只对两个表个访问了1次，所以，效率更高。