SQL> SQL> SELECT extract(DAY FROM a.paydate) everyday, COUNT(DECLNO), a.dept_id 2 FROM tt, 3 (SELECT * 4 FROM (SELECT DISTINCT DEPT_ID FROM tt), 5 (SELECT LEVEL + DATE '2010-05-22' - 1 PAYDATE 6 FROM dual 7 CONNECT BY LEVEL <= DATE '2010-05-25' - DATE '2010-05-22' + 1)) a 8 WHERE a.DEPT_ID = tt.DEPT_ID(+) 9 AND a.PAYDATE = tt.PAYDATE(+) 10 GROUP BY a.dept_id, extract(DAY FROM a.paydate) 11 ORDER BY extract(DAY FROM a.paydate), a.dept_id;
DEPT_ID DECLNO DEPT_NAME PAYDATE
------- --------------- --------- -----------
1001 380000205000165 轻纺 2010-5-23
1002 化矿 2010-5-23
1003 380000205000165 机电 2010-5-23
SQL>
SQL> SELECT extract(DAY FROM a.paydate) everyday, COUNT(DECLNO), a.dept_id
2 FROM tt,
3 (SELECT *
4 FROM (SELECT DISTINCT DEPT_ID FROM tt),
5 (SELECT LEVEL + DATE '2010-05-22' - 1 PAYDATE
6 FROM dual
7 CONNECT BY LEVEL <= DATE '2010-05-25' - DATE '2010-05-22' + 1)) a
8 WHERE a.DEPT_ID = tt.DEPT_ID(+)
9 AND a.PAYDATE = tt.PAYDATE(+)
10 GROUP BY a.dept_id, extract(DAY FROM a.paydate)
11 ORDER BY extract(DAY FROM a.paydate), a.dept_id;
EVERYDAY COUNT(DECLNO) DEPT_ID
---------- ------------- -------
22 0 1001
22 0 1002
22 0 1003
23 1 1001
23 0 1002
23 1 1003
24 0 1001
24 0 1002
24 0 1003
25 0 1001
25 0 1002
25 0 1003
12 rows selected
SQL>
结果集tt中,没有发生业务的部门对应的paydate也是空的,另外,部门的id可能不练习