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 cFROM    tbl_kokyaku_kyuyoWHERE    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  bFROM    tbl_kokyaku_kyuyoWHERE    ko_cd = '000002'

　　

 用一个表做实验。

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

SELECTcase WHEN `user`.id = '01' THEN 'guanliyuan' ELSE 'laji' END AS Id,`user`.`name`,`user`.ageFROM `user`

查询结果：

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

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