mysql case统计

mac2025-02-08  16

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;

最新回复(0)