表t_service_detail有字段：
service_date
tax_id
hall_no
其他字段略去。
现在客户是选择一个时间段，比如：
开始时间：2011-10-10 结束时间：2011-10-28
按日期统计 hall_no为1时 tax_id为0 每天的总数，效果如下：
service_date count(总数)
2011-10-10 1
2011-10-11 1
2011-10-12 0 (表中不存在service_date为2011-10-12的记录也要列出来 总数计0 下同)
2011-10-13 0
2011-10-14 2
2011-10-15 2
2011-10-16 0
2011-10-17 5
...(每一天)
2011-10-28 3
结束
------解决方案--------------------
- SQL code
--DATE '2011-10-10' 这是你说的开始时间--DATE '2011-10-28' 这是你说的结束时间--BETWEEN DATE '2011-10-10' AND DATE '2011-10-28'--CONNECT BY LEVEL <= DATE '2011-10-28' - DATE '2011-10-10'--DATE '2011-10-10' + LEVEL - 1SELECT T.DTIME, COUNT(CASE WHEN A.HALL_NO = 1 AND A.TAX_ID = 0 THEN 1 ELSE NULL END) CNT FROM (SELECT * FROM T_SERVICE_DETAIL WHERE SERVICE_DATE BETWEEN DATE '2011-10-10' AND DATE '2011-10-28') A RIGHT JOIN (SELECT DATE '2011-10-10' + LEVEL - 1 DTIME FROM DUAL CONNECT BY LEVEL <= DATE '2011-10-28' - DATE '2011-10-10') T ON A.SERVICE_DATE = T.DTIME GROUP BY T.DTIME ORDER BY T.DTIME;--测试[SYS@orcl] SQL>WITH t_service_detail AS( 2 SELECT DATE'2011-10-10' service_date,0 tax_id,1 hall_no FROM dual UNION ALL 3 SELECT DATE'2011-10-10' service_date,0 tax_id,2 hall_no FROM dual UNION ALL 4 SELECT DATE'2011-10-11' service_date,0 tax_id,1 hall_no FROM dual UNION ALL 5 SELECT DATE'2011-10-11' service_date,0 tax_id,1 hall_no FROM dual UNION ALL 6 SELECT DATE'2011-10-13' service_date,0 tax_id,1 hall_no FROM dual UNION ALL 7 SELECT DATE'2011-10-15' service_date,0 tax_id,1 hall_no FROM dual UNION ALL 8 SELECT DATE'2011-10-16' service_date,0 tax_id,1 hall_no FROM dual UNION ALL 9 SELECT DATE'2011-10-18' service_date,0 tax_id,1 hall_no FROM dual UNION ALL 10 SELECT DATE'2011-10-19' service_date,0 tax_id,1 hall_no FROM dual UNION ALL 11 SELECT DATE'2011-10-19' service_date,0 tax_id,1 hall_no FROM dual UNION ALL 12 SELECT DATE'2011-10-10' service_date,0 tax_id,1 hall_no FROM dual) 13 SELECT T.DTIME, 14 COUNT(CASE WHEN A.HALL_NO = 1 AND A.TAX_ID = 0 THEN 1 ELSE NULL END) CNT 15 FROM (SELECT * 16 FROM T_SERVICE_DETAIL 17 WHERE SERVICE_DATE BETWEEN DATE '2011-10-10' AND DATE '2011-10-28') A 18 RIGHT JOIN (SELECT DATE '2011-10-10' + LEVEL - 1 DTIME 19 FROM DUAL 20 CONNECT BY LEVEL <= DATE '2011-10-28' - DATE '2011-10-10') T 21 ON A.SERVICE_DATE = T.DTIME 22 GROUP BY T.DTIME 23 ORDER BY T.DTIME;DTIME CNT---------- ----------2011-10-10 22011-10-11 22011-10-12 02011-10-13 12011-10-14 02011-10-15 12011-10-16 12011-10-17 02011-10-18 12011-10-19 22011-10-20 02011-10-21 02011-10-22 02011-10-23 02011-10-24 02011-10-25 02011-10-26 02011-10-27 0已选择18行。
------解决方案--------------------
- SQL code
select m.service_date , nvl(n.cnt,0) cnt from( SELECT to_date('2011-10-10' , 'YYYY-MM-DD') + ROWNUM - 1 service_date FROM dual CONNECT BY ROWNUM <= (to_date('2011-10-28' , 'YYYY-MM-DD') - to_date('2011-10-10' , 'YYYY-MM-DD') + 1);) mleft join( select service_date , count(1) cnt from t_service_detail where hall_no = 1 and tax_id = 0 group by service_date) non m.service_date = n.service_date
------解决方案--------------------
实测成功：
- SQL code
CREATE TABLE t_service_detail( service_date DATE, tax_id NUMBER(4), hall_no NUMBER(4));INSERT INTO t_service_detail VALUES(to_date('2011-10-10', 'YYYY-MM-DD'), 0, 1);INSERT INTO t_service_detail VALUES(to_date('2011-10-10', 'YYYY-MM-DD'), 1, 0);INSERT INTO t_service_detail VALUES(to_date('2011-10-11', 'YYYY-MM-DD'), 0, 1);INSERT INTO t_service_detail VALUES(to_date('2011-10-14', 'YYYY-MM-DD'), 0, 1);INSERT INTO t_service_detail VALUES(to_date('2011-10-14', 'YYYY-MM-DD'), 0, 1);INSERT INTO t_service_detail VALUES(to_date('2011-10-14', 'YYYY-MM-DD'), 1, 0);INSERT INTO t_service_detail VALUES(to_date('2011-10-15', 'YYYY-MM-DD'), 0, 1);INSERT INTO t_service_detail VALUES(to_date('2011-10-15', 'YYYY-MM-DD'), 0, 1);INSERT INTO t_service_detail VALUES(to_date('2011-10-15', 'YYYY-MM-DD'), 1, 0);INSERT INTO t_service_detail VALUES(to_date('2011-10-17', 'YYYY-MM-DD'), 0, 1);INSERT INTO t_service_detail VALUES(to_date('2011-10-17', 'YYYY-MM-DD'), 0, 1);INSERT INTO t_service_detail VALUES(to_date('2011-10-17', 'YYYY-MM-DD'), 0, 1);INSERT INTO t_service_detail VALUES(to_date('2011-10-17', 'YYYY-MM-DD'), 0, 1);INSERT INTO t_service_detail VALUES(to_date('2011-10-17', 'YYYY-MM-DD'), 0, 1);INSERT INTO t_service_detail VALUES(to_date('2011-10-17', 'YYYY-MM-DD'), 1, 0);SELECT service_date,NVL(n.cnt, 0) "汇总" FROM (SELECT TO_DATE('2011-10-10', 'YYYY-MM-DD') + ROWNUM - 1 service_date FROM dual CONNECT BY ROWNUM <= TO_DATE('2011-10-28', 'YYYY-MM-DD') - TO_DATE('2011-10-10', 'YYYY-DD-MM') + 1) mLEFT JOIN (SELECT service_date, COUNT(1) cnt FROM t_service_detail WHERE tax_id = 0 AND hall_no = 1 GROUP BY service_date) nUSING (service_date) ORDER BY service_date;