SQL 中case when then else 用法

SQL如下：

SELECT DISTINCT
sy_haken_type,
sy_sagyo_type,
sy_kokyaku_cd
FROm
tbl_syukei
WHERE
(sy_sagyo_ymd between '2010-01-01' AND '2012-12-30')AND
(sy_staff_cd <> '' AND sy_staff_cd is not null) AND
(sy_kokyaku_cd <> '' AND sy_kokyaku_cd is not null) AND
sy_kokyaku_cd='800001'
ORDER BY
sy_kokyaku_cd

原SQL是这样的，然后将查询的结果再进行过滤出，所以这里为了解决内存，和释放SQL，就直接在SQL里面进行一次过滤，直接将外部的逻辑加入到SQL语句中

SELECT DISTINCT
    (case when ((sy_haken_type='D' or sy_haken_type is null or sy_haken_type='')and sy_sagyo_type='0') =true  then '1' else '0' end ) as daliy,
    (case when (sy_sagyo_type in ('1','2'))=true then '1' else '0' end) as  haken,
    (case when ((sy_haken_type in('J','S')) and sy_sagyo_type='0')=true then '1' else '0' end) as  short,
    (case when ((sy_haken_type in('M','L')) and sy_sagyo_type='0')=true then '1' else '0' end) as  ful,
    sy_kokyaku_cd
FROM
    tbl_syukei
WHERE
    (sy_sagyo_ymd  between '2010-01-01' AND    '2012-12-30')AND
    (sy_staff_cd <> '' AND sy_staff_cd is not null) AND
    (sy_kokyaku_cd <> '' AND sy_kokyaku_cd is not null) AND
    sy_kokyaku_cd='800001'
ORDER BY
    sy_kokyaku_cd

查出的结果如下：

 

这里case when 的使用性质就如同if ，

假如 case when ((sy_haken_type='D' or sy_haken_type is null or sy_haken_type='')and sy_sagyo_type='0') =true  

 then '1'

else '0'

end

 

 

SELECT
    (case  gensen_type when '00' then 'aa'       
            when '01' then 'xx'                
   when '10' then 'bb'
 else 'more' end ) as c
FROM
    tbl_kokyaku_kyuyo
WHERE
    ko_cd = '000002'

//两种写法，返回一个值和三个值

SELECT
    (case  when gensen_type='00' then 'xxx' else 'bbb' end) as c,
    (case  when gensen_type='01' then 'xxx' else 'bbb' end)  as a,
    (case  when gensen_type='10' then 'xxx' else 'bbb' end)  as  b
FROM
    tbl_kokyaku_kyuyo
WHERE
    ko_cd = '000002'

　　

 用一个表做实验。

将每条数据都进行下过滤的时候也可以用case when

SELECT
case WHEN `user`.id = '01' THEN 'guanliyuan' ELSE 'laji' END AS Id,
`user`.`name`,
`user`.age
FROM `user`

查询结果：

这里的语法和VB有点像，if 为真then 一个表达式，else 一个表达式，然后end结束，

这个语法能将很多的数据进行一遍过滤