1-5 外连接的用法-SQL进阶教程

用外连接进行行列转换 (1)（行→列）：制作交叉表

-- 水平展开求交叉表(1)：使用外连接
select C0.name,
(case when C1.name is not null then 'o' else null end) 'SQL入门',
(case when C2.name is not null then 'o' else null end) 'UNIX基础',
(case when C3.name is not null then 'o' else null end) 'Java中级'
from (select distinct name from Courses) C0
left join (select name from Courses where course='SQL入门') C1
on C0.name=C1.name
left join (select name from Courses where course='UNIX基础') C2
on C0.name=C2.name
left join (select name from Courses where course='Java中级') C3
on C0.name=C3.name;

-- 水平展开(2)：使用标量子查询
select C0.name,
(select 'o' from Courses C1 where course='SQL入门' and C1.name=C0.name