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 = '01' THEN 'guanliyuan' ELSE 'laji' END AS Id,
`user`.`name`,
`user`.age
FROM `user`

查詢結果：

SQL 中case when then else 用法

這裡的文法和VB有點像，if 為真then 一個表達式，else 一個表達式，然後end結束，

這個文法能将很多的資料進行一遍過濾