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;
