前面两篇文章写的是,如何把 标量子查询、聚合标量子查询,转为 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次,所以,效率更高。