case when 实例

使用case when优化sql示例

优化前：

select t1.userid, t1.countSize, t2.nearSize from (select  distinct userid, count(1) as countSize  from t_conclusion where userid is not null group by userid) t1

left join

(select  distinct userid, count(1)as  nearSize from t_conclusion where enddate-3 < sysdate and enddate > sysdate group by userid) t2

on t1.userid = t2.userid

 

优化后：

SELECT

       userid,

       COUNT (1) AS countSize,

       COUNT (

              CASE

              WHEN enddate - 3 < SYSDATE

              AND enddate > SYSDATE THEN

                     1

              ELSE

                     NULL

              END

       ) AS nearSize

FROM

       t_conclusion

where userid is not null

GROUP BY

       userid

 

使用case when 统计重复数据

SELECT  COUNT ( CASE WHEN tci.ITEMID in(1,2,3)  AND tci.TYPE = 1 THEN 1 ELSE NULL END )+

       COUNT ( CASE WHEN tci.ITEMID in(1,2,3)  AND tci.TYPE = 0 THEN 1 ELSE NULL END )

FROM t_company_item tci where tci.COMPANY_NAME != 'XXX'