日期:2014-05-17  浏览次数:20914 次

求按按日期统计sql
表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 - 1

SELECT 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          2
2011-10-11          2
2011-10-12          0
2011-10-13          1
2011-10-14          0
2011-10-15          1
2011-10-16          1
2011-10-17          0
2011-10-18          1
2011-10-19          2
2011-10-20          0
2011-10-21          0
2011-10-22          0
2011-10-23          0
2011-10-24          0
2011-10-25          0
2011-10-26          0
2011-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);
) m
left join
(
  select service_date , count(1) cnt from t_service_detail where hall_no = 1 and tax_id = 0 group by service_date
) n
on 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', 'YYY