外连接的用法

末蓝、 2022-04-16 00:29 211阅读 0赞

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5OTQwMjA1_size_16_color_FFFFFF_t_70][]

由左表得出右边的效果.

用CASE表达式可以玩实现,现在来使用标量子查询完成

![20181114200453138.png][]

select c.`name`, (select 'o' from `courses` c1 where c.name = c1.name and c1.`course`='UNIX基础') as unix,
    		  (select 'o' from `courses` c2 where c.name = c2.name and c2.`course`='Java中级') as java,
    		  (select 'o' from `courses` c3 where c.name = c3.name and c3.`course`='SQL入门') as `sql`
    		from (select distinct name from `courses`) c;

CASE表达式的写法

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5OTQwMjA1_size_16_color_FFFFFF_t_70 1][]

select name,case when sum(case when `course`='UNIX基础' then 1 else 0 end)=1 then 'o' else null end as UNIX基础 ,
                case when sum(case when `course`='Java中级' then 1 else 0 end)=1 then 'o' else null end as Java中级 ,
                case when sum(case when `course`='SQL入门' then 1 else 0 end)=1 then 'o' else null end as SQL入门 
                from `courses` group by`name`;
                
    select c.name,case when c.name in (select c1.name from `courses` c1 where c1.`course`='UNIX基础') then 'o' else null end as UNIX基础,
                  case when c.name in (select c1.name from `courses` c1 where c1.`course`='Java中级') then 'o' else null end as Java中级,
                  case when c.name in (select c1.name from `courses` c1 where c1.`course`='SQL入门') then 'o' else null end as SQL入门
                  from `courses` c group by c.name;

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5OTQwMjA1_size_16_color_FFFFFF_t_70]: /images/20220416/0fdb74e9172f40c0a08289f3e6931719.png
[20181114200453138.png]: /images/20220416/815141a05a704831a5fbf807d29e6424.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5OTQwMjA1_size_16_color_FFFFFF_t_70 1]: /images/20220416/b5f2502ae9c343ffa860e19afc46dc86.png