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) 编辑 收藏