SQL 中case when then else 用法

时间:2023-03-08 16:07:03
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 中case when then else 用法

原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

查出的结果如下:

SQL 中case when then else 用法

这里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'

  

用一个表做实验。

SQL 中case when then else 用法

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

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

查询结果:

SQL 中case when then else 用法

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

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