17,086
社区成员
发帖
与我相关
我的任务
分享请教一个SQL语句，自动为没有数据的月份填0
原始数据如下：
分组 时间 金额
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语句能否实现？
...全文
请发表友善的回复…
发表回复
xpingping 2011-12-27
- 打赏
- 举报
select 'A' 分组,t1.rn 月份,nvl(s,0) 金额 from
(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
cutebear2008 2011-12-27
- 打赏
- 举报
SELECT 分组,substr(时间,5,2) 月份,nvl(sum(金额),0) 金额
FROM tb
group by substr(时间,5,2);
FROM tb
group by substr(时间,5,2);
oO寒枫Oo 2011-12-27
- 打赏
- 举报
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)
oO寒枫Oo 2011-12-27
- 打赏
- 举报
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')
oO寒枫Oo 2011-12-27
- 打赏
- 举报
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')
yugang1219 2011-12-27
- 打赏
- 举报
分组有可能有B的情况，11年之前的数据也有，我只是简化了，主要是不知道怎么自动为没有数据的月份填0
oO寒枫Oo 2011-12-27
- 打赏
- 举报
没有11年之前的数据吗？
realliguangyu82 2011-12-27
- 打赏
- 举报
分组，还有B的情况吗？还是只有A这种情况
Alessandro_ 2011-12-27
- 打赏
- 举报
[Quote=引用 13 楼 hj_daxian 的回复:]
查询今年12个月的数据 未出现的月份 不知道属于那个分组 所以就显示空了
SQL code
WITH T1 AS(
SELECT ADD_MONTHS(DATE'2010-12-01',LEVEL) AS t_date
FROM DUAL CONNECT BY LEVEL <= 12
),T2 AS(SELECT 'A' 分组,'20110706' 时间 , 100……
[/Quote]
+
查询今年12个月的数据 未出现的月份 不知道属于那个分组 所以就显示空了
SQL code
WITH T1 AS(
SELECT ADD_MONTHS(DATE'2010-12-01',LEVEL) AS t_date
FROM DUAL CONNECT BY LEVEL <= 12
),T2 AS(SELECT 'A' 分组,'20110706' 时间 , 100……
[/Quote]
+
大明湖畔_帅锅 2011-12-27
- 打赏
- 举报
查询今年12个月的数据 未出现的月份 不知道属于那个分组 所以就显示空了
WITH T1 AS(
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
minitoy 2011-12-27
- 打赏
- 举报
left join
大明湖畔_帅锅 2011-12-27
- 打赏
- 举报
上面掉了个分组
WITH T1 AS
(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');
大明湖畔_帅锅 2011-12-27
- 打赏
- 举报
--表名为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');
qinglang1987 2011-12-27
- 打赏
- 举报
select 'A' 分组,t1.rn 月份,nvl(s,0) 金额 from
(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
