原始数据如下:
分组 时间 金额
A 20110706 100
A 20110711 200
A 20110712 100
A 20110719 400
A 20110801 100
A 20110809 540
A 20110826 160
A 20111006 190
A 20111007 210
A 20111012 109时间字段前四位为年,中间两位为月,最后两位为日要求得到的数据为按月份汇总的数据,没有数据的月份自动填0,结果如下:
分组 月份 金额
A 01 0
A 02 0
A 03 0
A 04 0
A 05 0
A 06 0
A 07 800
A 08 800
A 09 0
A 10 509
A 11 0
A 12 0用一条sql语句能否实现?
分组 时间 金额
A 20110706 100
A 20110711 200
A 20110712 100
A 20110719 400
A 20110801 100
A 20110809 540
A 20110826 160
A 20111006 190
A 20111007 210
A 20111012 109时间字段前四位为年,中间两位为月,最后两位为日要求得到的数据为按月份汇总的数据,没有数据的月份自动填0,结果如下:
分组 月份 金额
A 01 0
A 02 0
A 03 0
A 04 0
A 05 0
A 06 0
A 07 800
A 08 800
A 09 0
A 10 509
A 11 0
A 12 0用一条sql语句能否实现?
select b.分组 ,to_char(b.时间,'yyyymm') as 月份,sum(nvl(b.金额,0)) as 金额
from
(SELECT to_char(TO_DATE('20110101','yyyymmdd' ) +LEVEL - 1,'yyyymmdd') as dt
FROM DUAL
CONNECT BY LEVEL <=
TRUNC(TO_DATE('20120101', 'yyyymmdd')-TO_DATE('20110101', 'yyyymmdd')) + 1 ) a left join tab b on a.dt=b.to_char(时间,'yyyymmdd')
group by b.分组 ,to_char(b.时间,'yyyymm')
select b.分组 ,to_char(b.时间,'yyyymm') as 月份,sum(nvl(b.金额,0)) as 金额
from
(SELECT to_char(TO_DATE('20110101','yyyymmdd' ) +LEVEL - 1,'yyyymmdd') as dt
FROM DUAL
CONNECT BY LEVEL <=
TRUNC(TO_DATE('20120101', 'yyyymmdd')-TO_DATE('20110101', 'yyyymmdd')) + 1 ) a left join tab b on a.dt=to_char(b.时间,'yyyymmdd')
group by b.分组 ,to_char(b.时间,'yyyymm')
select substr(a.dt,1,4) as 月份,sum(nvl(b.金额,0)) as 金额
from
(SELECT to_char(TO_DATE('20110101','yyyymmdd' ) +LEVEL - 1,'yyyymmdd') as dt
FROM DUAL
CONNECT BY LEVEL <=
TRUNC(TO_DATE('20120101', 'yyyymmdd')-TO_DATE('20110101', 'yyyymmdd')) + 1 ) a left join tab b on a.dt=to_char(b.时间,'yyyymmdd')
group by substr(a.dt,1,4)
FROM tb
group by substr(时间,5,2);
(select lpad(rownum,2,'0') rn from dual connect by rownum<=12)t1
,
(select substr(to_char(时间,'YYYYMMDD'),5,2) rn ,sum(金额) s from tb
group by substr(to_char(时间,'YYYYMMDD'),5,2))t2
where t1.rn=t2.rn(+) order by t1.rn
(select lpad(rownum,2,'0') rn from dual connect by rownum<=12)t1
,
(select substr(to_char(时间,'YYYYMMDD'),5,2) rn ,sum(金额) s from tb
group by substr(to_char(时间,'YYYYMMDD'),5,2))t2
where t1.rn=t2.rn(+) order by t1.rn
--表名为TB1 构造一个月份表T1来连接TB1查询每月数据
WITH T1 AS
(SELECT ADD_MONTHS(DATE '2011-01-01', LEVEL) AS COL1
FROM DUAL
CONNECT BY LEVEL <= 11)
SELECT TO_CHAR(T1.T_DATE, 'YYYY-MM'), NVL(SUM(TB1.金额))
FROM T1
LEFT JOIN TB1
ON TO_CHAR(TO_DATE(TB1.时间,'YYYY-MM-DD'), 'YYYY-MM') = TO_CHAR(T1.T_DATE, 'YYYY-MM')
GROUP BY TO_CHAR(T1.T_DATE, 'YYYY-MM');
(SELECT ADD_MONTHS(DATE '2011-01-01', LEVEL) AS COL1
FROM DUAL
CONNECT BY LEVEL <= 11)
SELECT TB1.分组,TO_CHAR(T1.T_DATE, 'YYYY-MM'), NVL(SUM(TB1.金额),0)
FROM T1
LEFT JOIN TB1
ON TO_CHAR(TO_DATE(TB1.时间,'YYYY-MM-DD'), 'YYYY-MM') = TO_CHAR(T1.T_DATE, 'YYYY-MM')
GROUP BY TB1.分组,TO_CHAR(T1.T_DATE, 'YYYY-MM');
SELECT ADD_MONTHS(DATE'2010-12-01',LEVEL) AS t_date
FROM DUAL CONNECT BY LEVEL <= 12
),T2 AS(SELECT 'A' 分组,'20110706' 时间 , 100 金额 FROM DUAL UNION ALL
SELECT 'A' 分组,'20110711' 时间 , 200 金额 FROM DUAL UNION ALL
SELECT 'A' 分组,'20110712' 时间 , 100 金额 FROM DUAL UNION ALL
SELECT 'A' 分组,'20110719' 时间 , 400 金额 FROM DUAL UNION ALL
SELECT 'A' 分组,'20110801' 时间 , 100 金额 FROM DUAL UNION ALL
SELECT 'A' 分组,'20110809' 时间 , 540 金额 FROM DUAL UNION ALL
SELECT 'A' 分组,'20110826' 时间 , 160 金额 FROM DUAL UNION ALL
SELECT 'A' 分组,'20111006' 时间 , 190 金额 FROM DUAL UNION ALL
SELECT 'A' 分组,'20111007' 时间 , 210 金额 FROM DUAL UNION ALL
SELECT 'A' 分组,'20111012' 时间 , 109 金额 FROM DUAL
)SELECT T2.分组,TO_CHAR(T1.t_date,'YYYY-MM') 日期,NVL(SUM(T2.金额),0) 金额
FROM T1 LEFT JOIN T2 ON TO_CHAR(T1.t_date,'YYYY-MM') = TO_CHAR(TO_DATE(T2.时间,'YYYY_MM-DD'),'YYYY-MM')
GROUP BY T2.分组,TO_CHAR(T1.t_date,'YYYY-MM')
ORDER BY TO_CHAR(T1.t_date,'YYYY-MM')----------------------------------------------------------------
1 2011-01 0
2 2011-02 0
3 2011-03 0
4 2011-04 0
5 2011-05 0
6 2011-06 0
7 A 2011-07 800
8 A 2011-08 800
9 2011-09 0
10 A 2011-10 509
11 2011-11 0
12 2011-12 0