mysql case统计

SELECT 
    name as '小区名称',
    sum(case when location='inner' then 1 else 0 end) AS '小区内',
    sum(case when location='door' then 1 else 0 end) AS '小区门口', 
    sum(case when location='street' then 1 else 0 end) AS '街道旁',
    round(sum(case when location='inner' then 1 else 0 end)/(SELECT count(*) FROM v_shop_community)*100,1) AS '小区内%',
    round(sum(case when location='door' then 1 else 0 end)/(SELECT count(*) FROM v_shop_community)*100,1) AS '小区门口%',
    round(sum(case when location='street' then 1 else 0 end)/(SELECT count(*) FROM v_shop_community)*100,1) AS '街道旁%'
from v_shop_community group by name;
 

 

SQl很简单,先根据分类ID进行分组,然后再通过CASE WHEN 再统计不同文章状态数量

SELECT  t.name,t.parent,t.term_id,count(1) as count 

,COUNT( CASE WHEN  p.post_status  = 1 then 1 else null end ) as p1 # 1=已审核
,COUNT( CASE WHEN  p.post_status  = 0 then 0 else null end ) as p0 # 0=未审核
,COUNT( CASE WHEN  p.post_status  = -1 then -1 else null end ) as p_1 #-1=审核未通过

FROM

term_relationships r               
join terms t on r.term_id= t.term_id
join posts p on p.id = r.object_id
                                     
group by t.term_id #以分类ID分组

 

生成的效果：

 

 select S.syctime_day,
   sum(case when S.o_source = 'CDE' then 1 else 0 end) as 'CDE',
   sum(case when S.o_source = 'SDE' then 1 else 0 end) as 'SDE',
   sum(case when S.o_source = 'PDE' then 1 else 0 end) as 'PDE',
   sum(case when S.o_source = 'CSE' then 1 else 0 end) as 'CSE',
   sum(case when S.o_source = 'SSE' then 1 else 0 end) as 'SSE'
 from statistic_order S where S.syctime_day > '2015-05-01' and S.syctime_day < '2016-08-01' 
 GROUP BY S.syctime_day order by S.syctime_day asc;