CASE表达式的用法

小灰灰 2022-04-17 06:06 242阅读 0赞

CASE表达式的一大优势在于能够判断表达式.在CASE表达式里,可以使用BETWEEN,LIKE和<,>等,以及能够嵌套子查询的IN和EXISTS.

假设有张学生和课程表

![20181113232120786.png][]

怎么得到下图效果?

![20181113232155218.png][]

使用CASE表达式进行行列转换

![20181113232306835.png][]

select distinct a.name,case when a.name in (select c.name from `courses` c where c.course='UNIX基础') then '√' else '○' end as unix,
                           case when a.name in (select c.name from `courses` c where c.course='Java中级') then '√' else '○' end as java,
                           case when a.name in (select c.name from `courses` c where c.course='SQL入门') then '√' else '○' end as `sql`
     from `courses` a ;

简单CASE表达式的写法

CASE sex WHEN 1 THEN '男' WHEN 2 THEN '女' ELSE '未知' END

搜索CASE表达式的写法

CASE WHEN sex=1 THEN '男' WHEN sex=2 THEN '女' ELSE '其他' END

将已有编号方式转换为新的方式并统计

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5OTQwMjA1_size_16_color_FFFFFF_t_70][]

![20181113233801986.png][]

select case when `pref_name` in ('德岛','香川','爱媛','高知') then '四国' 
                when `pref_name` in ('佐贺','长崎','福冈') then '九州'
                else '其他' end as 地区名,sum(`population`) as '人口' from `poptbl` group by 地区名;

进行不同条件的统计是CASE表达式的著名用法之一

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5OTQwMjA1_size_16_color_FFFFFF_t_70 1][]

![20181113234615907.png][]

select `pref_name`,sum(case when sex=1 then `population` else 0 end)as '男',
                       sum(case when sex=2 then `population` else 0 end)as '女' 
                       from `poptbl2` group by `pref_name`;

这里是将行结构的数据转换为列结构的数据.除了sum, count.avg等聚合函数也可以将行结构转换为列结构.学会用SELECT子句进行条件分支.

在UPDATE语句里进行条件分支

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5OTQwMjA1_size_16_color_FFFFFF_t_70 2][]

![20181114001739489.png][]

update `salaries` set `salary`= case when `salary`>=300000 then `salary`*0.9
                                         when `salary`>=250000 and `salary`<280000 then `salary`*1.2
                                         else `salary` end;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5OTQwMjA1_size_16_color_FFFFFF_t_70 3][]

![20181114000612562.png][]

select std_id,case when count(1)=1   -- 只加入了一个俱乐部的学生
                            then `club_id`
                            else max(case when `main_club_flg`='Y' then `club_id` else 0 end) end as 俱乐部
                            from `studentclub` group by `std_id`;

![20181114000844655.png][]

这条SQL语句在CASE表达式里使用了聚合函数,又在聚合函数里使用了CASE表达式,主要目的是用CASE WHEN COUNT(1)=1......ELSE..... 这样的CASE表达式来表示"只加入了一个俱乐部还是多个俱乐部"这样的条件分支.

CASE表达式用在SELECT子句时,既可以写在聚合函数内部,也可以写在聚合函数外部.这种高度自由的写法正是CASE表达式的强大之处.

[20181113232120786.png]: /images/20220417/7af2dbcab4134735b566fbcb2834dee1.png
[20181113232155218.png]: /images/20220417/1625bb3b510a43db81a5eb975e84b1fa.png
[20181113232306835.png]: /images/20220417/20ca12c1c25540a6a4d0e8d395fbc11a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5OTQwMjA1_size_16_color_FFFFFF_t_70]: /images/20220417/e51cd65f07434af5a7f60e3a94556450.png
[20181113233801986.png]: /images/20220417/10b9c543d1a2479f82f200560f239fba.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5OTQwMjA1_size_16_color_FFFFFF_t_70 1]: /images/20220417/357f97b81cec4a5d9951cd791e0dd74d.png
[20181113234615907.png]: /images/20220417/eaa3c735dd6040949a73b1e34f2c38da.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5OTQwMjA1_size_16_color_FFFFFF_t_70 2]: /images/20220417/1ca5edb7e6a5451fa881618116db9a95.png
[20181114001739489.png]: /images/20220417/041a1dff4ad44ee9b7594eaf94bd5f70.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5OTQwMjA1_size_16_color_FFFFFF_t_70 3]: /images/20220417/7e5ace20584d469c9f15acb8bbf6ca23.png
[20181114000612562.png]: /images/20220417/60fa2270b7404eb3bd2b22e7223366a3.png
[20181114000844655.png]: /images/20220417/e8fcfbbca22249bb9c8a22c3605ff176.png