两种sql的使用方法 横着和竖着的月份查询

两种sql的使用方法

 

SELECT distinct case when t.c=0 then 0 ELSE(t2.c-t.c)/t.c end*100 as c,'同比' as year,t.month

FROM (select 0 as c,a.m as month from
(select 1 as m
UNION
select 2 as m
union
select 3 as m
union
select 4 as m
UNION
select 5 as m
UNION
select 6 as m
union
select 7 as m
UNION
select 8 as m
union
select 9 as m
union
select 10 as m
UNION
select 11 as m
UNION
select 12 as m
) a
where a.m not in
(select distinct month(buildDate) from tm_case where YEAR(buildDate)=2015 ))  t
LEFT JOIN (select count(*) as c,month(buildDate) as month from tm_case where YEAR(buildDate)=2016 group by month(buildDate)) t2
ON  t.month=t2.month) a


select s.caseProperty,d.ddValue,
   sum(case when SUBSTRING(S.buildDate, 6 ,2) = '01' then '1' else 0 end) as '1月',
   sum(case when SUBSTRING(S.buildDate, 6 ,2) = '02' then '1' else 0 end) as '2月',
   sum(case when SUBSTRING(S.buildDate, 6 ,2) = '03' then '1' else 0 end) as '3月',
   sum(case when SUBSTRING(S.buildDate, 6 ,2) = '04' then '1' else 0 end) as '4月',
   sum(case when SUBSTRING(S.buildDate, 6 ,2) = '05' then '1' else 0 end) as '5月',
   sum(case when SUBSTRING(S.buildDate, 6 ,2) = '06' then '1' else 0 end) as '6月',
   sum(case when SUBSTRING(S.buildDate, 6 ,2) = '07' then '1' else 0 end) as '7月',
   sum(case when SUBSTRING(S.buildDate, 6 ,2) = '08' then '1' else 0 end) as '8月',
   sum(case when SUBSTRING(S.buildDate, 6 ,2) = '09' then '1' else 0 end) as '9月',
   sum(case when SUBSTRING(S.buildDate, 6 ,2) = '10' then '1' else 0 end) as '10月',
   sum(case when SUBSTRING(S.buildDate, 6 ,2) = '11' then '1' else 0 end) as '11月',
   sum(case when SUBSTRING(S.buildDate, 6 ,2) = '12' then '1' else 0 end) as '12月'
 from tm_case S LEFT JOIN pd_datadict_detail d ON s.caseProperty=d.id
   WHERE SUBSTRING(s.buildDate,1,4)='2012'
 GROUP BY d.ddValue;

横排数据

SELECT COUNT(CASE WHEN order_status=1 THEN 0 END) as 'unpayCount'
,COUNT(CASE WHEN order_status=2 THEN 0 END) as 'waitingCount'
,COUNT(CASE WHEN order_status=3 THEN 0 END) as 'unusedCount'
,COUNT(CASE WHEN order_status=4 THEN 0 END) as 'finishCount'
,COUNT(CASE WHEN order_status=5 THEN 0 END) as 'applyCount'
,COUNT(CASE WHEN order_status=6 THEN 0 END) as 'refundCount'
,COUNT(CASE WHEN order_status=7 THEN 0 END) as 'cancelCount'
FROM tb_order