select amonth, count(acount) from ( (select 1 amonth,0 acount from dual union all ... select 12 amonth,0 acount from dual)unionselect substr(adate,5,1) amonth, count(id) acount from table1 group by substr(adate,5,1) ) tab group by amonth
oracle: select to_char(adate,'yyyymm') as month, count(1) from T group by to_char(adate,'yyyymm');但是如果某个月没数据,就不会有那个月的记录了。
select n, sum(nvl2(d,1,0)) from ( select trunc(adate)d ,n from 表1 ,(select level n from dual connect by level<=12)t where trunc(adate) = add_months(trunc(adate,'yy'),n-1)) group by n
SQL> WITH tb AS ( 2 SELECT '1' tid,'n1' tname,'2011.3.11' tdate FROM DUAL UNION ALL 3 SELECT '2' tid,'n2' tname,'2011.4.11' tdate FROM DUAL UNION ALL 4 SELECT '3' tid,'n3' tname,'2011.3.21' tdate FROM DUAL 5 ), 6 calendar_tb AS ( 7 SELECT LEVEL mon FROM DUAL CONNECT BY LEVEL <= 12 8 ) 9 SELECT t1.mon, 10 NVL(t2.cnt, 0) cnt 11 FROM calendar_tb t1 12 LEFT JOIN (SELECT TO_NUMBER(TO_CHAR(TO_DATE(tb.tdate, 'yyyy/mm/dd'), 'mm')) mon, 13 COUNT(*) cnt 14 FROM tb 15 GROUP BY TO_NUMBER(TO_CHAR(TO_DATE(tb.tdate, 'yyyy/mm/dd'), 'mm')) 16 ) t2 17 ON t1.mon = t2.mon 18 ORDER BY t1.mon 19 ; MON CNT ---------- ---------- 1 0 2 0 3 2 4 1 5 0 6 0 7 0 8 0 9 0 10 0 11 0 12 012 rows selected
with test as (select 1 id, 'n1' name, to_date('2011.3.11', 'yyyy.mm.dd') adate from dual union select 2 id, 'n2' name, to_date('2011.4.12', 'yyyy.mm.dd') adate from dual union select 3 id, 'n3' name, to_date('2011.3.21', 'yyyy.mm.dd') adate from dual) select a.*, b.* from test a right join (select rownum no from dual connect by rownum < =12) b on to_number(to_char(a.adate, 'mm'))=b.no
加个group就可以了
select to_char(adate,'yyyymm') as month, count(1) from T group by to_char(adate,'yyyymm');
-- 数据 create table t_test as select * from ( select 1 as id, 'n1' as name, to_date('2011-03-01','yyyy-mm-dd') as aDate from dual union all select 2, 'n2', to_date('2011-04-01','yyyy-mm-dd') from dual union all select 23, 'n3', to_date('2011-03-04','yyyy-mm-dd') from dual) --查询 select a.*,nvl(counted,0) from (select rownum a from dual connect by rowNum <= 12) a left join (select to_char(aDate, 'mm') monthNum, count(1) as counted from t_test group by to_char(aDate, 'mm')) t on a.a = t.monthNum order by a
你那个数量是不是出现的次数?如果是的话可以 select to_char(adate,'mm'),count(to_char(adate,'mm')) from 表名 group by to_char(adate,'mm') order by to_char(adate,'mm')
SELECT MON.M AS "月份", NVL(V.CNT, 0) AS "数量" FROM (SELECT LEVEL M FROM DUAL CONNECT BY LEVEL < 13) MON, (SELECT TO_CHAR(A.ADATE, 'MM') MM, COUNT(1) AS CNT FROM 表1 A GROUP BY TO_CHAR(A.ADATE, 'MM')) V WHERE MON.M = V.MM(+) ORDER BY MON.M
select amonth, count(acount) from
(
(select 1 amonth,0 acount from dual union all
...
select 12 amonth,0 acount from dual)unionselect substr(adate,5,1) amonth, count(id) acount
from table1
group by substr(adate,5,1)
) tab
group by amonth
select to_char(adate,'yyyymm') as month, count(1) from T group by to_char(adate,'yyyymm');但是如果某个月没数据,就不会有那个月的记录了。
where trunc(adate) = add_months(trunc(adate,'yy'),n-1)) group by n
SQL> WITH tb AS (
2 SELECT '1' tid,'n1' tname,'2011.3.11' tdate FROM DUAL UNION ALL
3 SELECT '2' tid,'n2' tname,'2011.4.11' tdate FROM DUAL UNION ALL
4 SELECT '3' tid,'n3' tname,'2011.3.21' tdate FROM DUAL
5 ),
6 calendar_tb AS (
7 SELECT LEVEL mon FROM DUAL CONNECT BY LEVEL <= 12
8 )
9 SELECT t1.mon,
10 NVL(t2.cnt, 0) cnt
11 FROM calendar_tb t1
12 LEFT JOIN (SELECT TO_NUMBER(TO_CHAR(TO_DATE(tb.tdate, 'yyyy/mm/dd'), 'mm')) mon,
13 COUNT(*) cnt
14 FROM tb
15 GROUP BY TO_NUMBER(TO_CHAR(TO_DATE(tb.tdate, 'yyyy/mm/dd'), 'mm'))
16 ) t2
17 ON t1.mon = t2.mon
18 ORDER BY t1.mon
19 ; MON CNT
---------- ----------
1 0
2 0
3 2
4 1
5 0
6 0
7 0
8 0
9 0
10 0
11 0
12 012 rows selected
1 id, 'n1' name, to_date('2011.3.11', 'yyyy.mm.dd') adate from dual
union
select
2 id, 'n2' name, to_date('2011.4.12', 'yyyy.mm.dd') adate from dual
union
select
3 id, 'n3' name, to_date('2011.3.21', 'yyyy.mm.dd') adate from dual)
select a.*, b.* from test a right join (select rownum no from dual connect by rownum < =12) b
on to_number(to_char(a.adate, 'mm'))=b.no
加个group就可以了
create table t_test as select * from (
select 1 as id, 'n1' as name, to_date('2011-03-01','yyyy-mm-dd') as aDate from dual
union all
select 2, 'n2', to_date('2011-04-01','yyyy-mm-dd') from dual
union all
select 23, 'n3', to_date('2011-03-04','yyyy-mm-dd') from dual)
--查询
select a.*,nvl(counted,0)
from (select rownum a from dual connect by rowNum <= 12) a
left join (select to_char(aDate, 'mm') monthNum, count(1) as counted
from t_test
group by to_char(aDate, 'mm')) t on a.a = t.monthNum
order by a
select to_char(adate,'mm'),count(to_char(adate,'mm')) from 表名 group by to_char(adate,'mm') order by to_char(adate,'mm')
SELECT MON.M AS "月份", NVL(V.CNT, 0) AS "数量"
FROM (SELECT LEVEL M FROM DUAL CONNECT BY LEVEL < 13) MON,
(SELECT TO_CHAR(A.ADATE, 'MM') MM, COUNT(1) AS CNT
FROM 表1 A
GROUP BY TO_CHAR(A.ADATE, 'MM')) V
WHERE MON.M = V.MM(+)
ORDER BY MON.M