解决方案 »
- 存储过程的相互调用、交叉问题,编译死锁?
- 8i exp/imp 导出导入到10G的时候有报错
- 给值为null的所有记录重新赋值
- oracle8不能导入,导出数据
- [求救]用java API连接oracle数据库的问题
- 优化sql語句
- 用sql loader导数 写ctl文件能否同时对两个表导数?
- pl/sql
- PRO*C开发的基于ORACLE8.0.5的程序如何在ORACLE8.1.7下运行?寻求改动量最小的解决办法(NT环境,UNIX环境下已经有解决办法了)
- 怎么把数据插入blob字段.
- select * from tbak where rownum <&dd order by 1 desc;
- 请教,ORACLE导出数据命令
字段意思 ?下面那个count咋得到的~
--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 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行。
(
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
(
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) m
LEFT JOIN
(SELECT service_date, COUNT(1) cnt FROM t_service_detail WHERE tax_id = 0 AND hall_no = 1 GROUP BY service_date) n
USING (service_date)
ORDER BY service_date;
结果: