一个表tb1 俩字段:t_date(时间格式:年月日),t_num(数量)添加几行数据
2011-07-04 5000
2011-07-25 6000
2011-08-08 900
2011-09-12 1200我现在这么写的
select to_char(trunc(a.t_date,'MM'),'YYYY-MM') 月份,sum(a.t_num) 合计
from tb1 a
group by trunc(a.t_date,'MM');
结果:
2011-07 11000
2011-08 900
2011-09 1200统计出来只有现有月份,请问如何写才能实现未出现月份统计数量为0
2011-07-04 5000
2011-07-25 6000
2011-08-08 900
2011-09-12 1200我现在这么写的
select to_char(trunc(a.t_date,'MM'),'YYYY-MM') 月份,sum(a.t_num) 合计
from tb1 a
group by trunc(a.t_date,'MM');
结果:
2011-07 11000
2011-08 900
2011-09 1200统计出来只有现有月份,请问如何写才能实现未出现月份统计数量为0
[TEST@myorcl] SQL>WITH T1 AS(
2 SELECT ADD_MONTHS(DATE'2010-12-01',LEVEL) AS COL1
3 FROM DUAL
4 CONNECT BY LEVEL <= 12
5 ),T2 AS(SELECT DATE'2011-07-04' AS COL2, 5000 COL3 FROM DUAL UNION ALL
6 SELECT DATE'2011-07-25' AS COL2, 6000 COL3 FROM DUAL UNION ALL
7 SELECT DATE'2011-08-08' AS COL2, 900 COL3 FROM DUAL UNION ALL
8 SELECT DATE'2011-09-12' AS COL2, 1200 COL3 FROM DUAL
9 )SELECT TO_CHAR(T1.COL1,'YYYY-MM'),
10 NVL(SUM(T2.COL3),0)
11 FROM T1 LEFT JOIN T2 ON TO_CHAR(T1.COL1,'YYYY-MM') = TO_CHAR(T2.COL2,'YYYY-MM')
12 GROUP BY TO_CHAR(T1.COL1,'YYYY-MM')
13 ORDER BY 1
14 ;TO_CHAR NVL(SUM(T2.COL3),0)
------- -------------------
2011-01 0
2011-02 0
2011-03 0
2011-04 0
2011-05 0
2011-06 0
2011-07 11000
2011-08 900
2011-09 1200
2011-10 0
2011-11 0
2011-12 0已选择12行。
刚学习oracle,看着不是太懂、 那我是不是把 T2 AS(...)改成我的表名就可以了?
貌似改成表后 报错:后面的select缺失关键字
要怎么关联到我的表呢?
直接把1楼的SQL拷下来执行就可以。
SELECT ADD_MONTHS(DATE'2010-12-01',LEVEL) AS COL1
FROM DUAL
connect by level <= 12
),T2 AS(SELECT t3 COL2, 5000 COL3 FROM test_yixl --test_yixl换成你自己的表名 )SELECT TO_CHAR(T1.COL1,'YYYY-MM'),
NVL(SUM(T2.COL3),0)
FROM T1 LEFT JOIN T2 ON TO_CHAR(T1.COL1,'YYYY-MM') = TO_CHAR(T2.COL2,'YYYY-MM')
GROUP BY TO_CHAR(T1.COL1,'YYYY-MM')
order by 1
--给你解释一下,主旨思路就是构造一个日期连续的表,然后以他为基础进行左连接。
--构造这个表的方法
SELECT ADD_MONTHS(DATE'2011-01-01',LEVEL) AS COL1
FROM DUAL
CONNECT BY LEVEL <= 11;--然后我给他起了个名字叫 t1
WITH T1 AS(
SELECT ADD_MONTHS(DATE'2011-01-01',LEVEL) AS COL1
FROM DUAL
CONNECT BY LEVEL <= 11
)--t2 是我构造的一个表,目的是模拟你的数据,你可以不用管,根据你写的sql我改下吧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.T_NUM))
FROM T1
LEFT JOIN TB1
ON TO_CHAR(T1.COL1, 'YYYY-MM') = TO_CHAR(TB1.T_DATE, 'YYYY-MM')
GROUP BY TO_CHAR(T1.T_DATE, 'YYYY-MM');