1.按年份查询
按年份分组查询后将结果按序排列
select to_char(SLRQ,'yyyy') ,count(*)from mv_ywst_fa group by to_char(SLRQ,'yyyy') ORDER BY to_char(SLRQ,'yyyy')
2.按季度查询
按季度分组查询后将结果按序排列
select to_char(SLRQ,'yyyy-Q') ,count(*)from mv_ywst_fa group by to_char(SLRQ,'yyyy-Q') ORDER BY to_char(SLRQ,'yyyy-Q')
3.按月份查询
按月份分组查询后将结果按序排列
select to_char(SLRQ,'yyyy-mm') ,count(*)from mv_ywst_fa group by to_char(SLRQ,'yyyy-mm') ORDER BY to_char(SLRQ,'yyyy-mm')
4.按周查询
按周分组查询后将结果按序排列
select to_char(SLRQ,'yyyy-IW') ,count(*)from mv_ywst_fa group by to_char(SLRQ,'yyyy-IW') ORDER BY to_char(SLRQ,'yyyy-IW')
5.分组查询并合并结果
将7个视图按季度分组查询并合并结果
SELECT time,sum(amount) amount FROM
(
select to_char(SLRQ,'yyyy-Q') as time,count(*) as amount from (SELECT * FROM mv_ywst_hs WHERE SLRQ between to_date('2012-1-1 00:00:00','yyyy-mm-dd hh24-mi-ss')
and to_date('2012-12-31 23:59:59','yyyy-mm-dd hh24-mi-ss')) group by to_char(SLRQ,'yyyy-Q')
union all
select to_char(SLRQ,'yyyy-Q') as time,count(*) as amount from (SELECT * FROM mv_ywst_gc WHERE SLRQ between to_date('2012-1-1 00:00:00','yyyy-mm-dd hh24-mi-ss')
and to_date('2012-12-31 23:59:59','yyyy-mm-dd hh24-mi-ss')) group by to_char(SLRQ,'yyyy-Q')
union all
select to_char(SLRQ,'yyyy-Q') as time,count(*) as amount from (SELECT * FROM mv_ywst_xz WHERE SLRQ between to_date('2012-1-1 00:00:00','yyyy-mm-dd hh24-mi-ss')
and to_date('2012-12-31 23:59:59','yyyy-mm-dd hh24-mi-ss')) group by to_char(SLRQ,'yyyy-Q')
union all
select to_char(SLRQ,'yyyy-Q') as time,count(*) as amount from (SELECT * FROM mv_ywst_yx WHERE SLRQ between to_date('2012-1-1 00:00:00','yyyy-mm-dd hh24-mi-ss')
and to_date('2012-12-31 23:59:59','yyyy-mm-dd hh24-mi-ss')) group by to_char(SLRQ,'yyyy-Q')
union all
select to_char(SLRQ,'yyyy-Q') as time,count(*) as amount from (SELECT * FROM mv_ywst_tj WHERE SLRQ between to_date('2012-1-1 00:00:00','yyyy-mm-dd hh24-mi-ss')
and to_date('2012-12-31 23:59:59','yyyy-mm-dd hh24-mi-ss')) group by to_char(SLRQ,'yyyy-Q')
union all
select to_char(SLRQ,'yyyy-Q') as time,count(*) as amount from (SELECT * FROM mv_ywst_fa WHERE SLRQ between to_date('2012-1-1 00:00:00','yyyy-mm-dd hh24-mi-ss')
and to_date('2012-12-31 23:59:59','yyyy-mm-dd hh24-mi-ss')) group by to_char(SLRQ,'yyyy-Q')
union all
select to_char(SLRQ,'yyyy-Q') as time,count(*) as amount from (SELECT * FROM mv_ywst_yd WHERE SLRQ between to_date('2012-1-1 00:00:00','yyyy-mm-dd hh24-mi-ss')
and to_date('2012-12-31 23:59:59','yyyy-mm-dd hh24-mi-ss')) group by to_char(SLRQ,'yyyy-Q')
) GROUP BY time