使用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'