随笔-71  评论-5  文章-0  trackbacks-0
  SELECT TO_CHAR(T.vc_ts,'YYYY') yearRe,TO_CHAR(T.vc_ts,'Q') timeRe,COUNT(*) countRe 
FROM table 
WHERE t.vc_status='0'
GROUP BY TO_CHAR(T.vc_ts,'Q'),TO_CHAR(T.vc_ts,'YYYY')


        


按年份的季度查询

select 
year as 年,
sum(case when quarter=1 then sale end) as 一季度,
sum(case when quarter=2 then sale end) as 二季度,
sum(case when quarter=3 then sale end) as 三季度,
sum(case when quarter=4 then sale end) as 四季度
from table_name
group by year

综合以上SQL

SELECT annual.YEAR,
SUM(CASE when annual.TIME='1' then annual.COUNT else 0 end ) AS 一季度,
SUM(CASE when annual.TIME='2' then annual.COUNT else 0 end ) AS 二季度,
SUM(CASE when annual.TIME='3' then annual.COUNT else 0 end ) AS 三季度,
SUM(CASE when annual.TIME='4' then annual.COUNT else 0 end ) AS 四季度
FROM(
SELECT TO_CHAR(T.vc_ts,'YYYY') YEAR,TO_CHAR(T.vc_ts,'Q') TIME,COUNT(*) COUNT
FROM table T
WHERE t.vc_status='0'
GROUP BY TO_CHAR(T.vc_ts,'Q'),TO_CHAR(T.vc_ts,'YYYY')
) annual GROUP BY annual.YEAR







posted on 2014-10-22 11:53 藤本蔷薇 阅读(2076) 评论(0)  编辑  收藏

只有注册用户登录后才能发表评论。


网站导航: