SQL 求助

[huan_lxyd]

有表t：
time
2012-3-6
2012-3-7
2012-3-8
2012-3-9

有起始时间 2012-3-3, 结束时间 2012-3-11 ，
如何获得在指定的时间段内表t中不存在的日期
结果为：
2012-3-4
2012-3-5
2012-3-10

[programmerxiaocai]

首先要通过时间段产生一个临时表，这个临时表是这个时间段内的所有时间。
然后从临时表中查询出不在已知表中的时间。

[youqi1984]

应该有比较的源表，包含所有的日期

[Seemv]

造数据做外连接
WITH t AS
(SELECT DATE '2012-3-6' TIME FROM dual
UNION ALL
SELECT DATE '2012-3-7' FROM dual
UNION ALL
SELECT DATE '2012-3-8' FROM dual
UNION ALL
SELECT DATE '2012-3-9' FROM dual),
t1 AS
(SELECT DATE '2012-03-03' + LEVEL - 1 TIME
FROM dual
CONNECT BY LEVEL <= 9)
SELECT t1.time
FROM t, t1
WHERE t.time(+) = t1.time
AND t.time IS NULL
ORDER BY 1;

[我心飞翔]

实测数据：

CREATE TABLE T146
(
    MyTime DATE
);
INSERT INTO T146 VALUES(to_date('2012-03-06', 'YYYY-MM-DD'));
INSERT INTO T146 VALUES(to_date('2012-03-07', 'YYYY-MM-DD'));
INSERT INTO T146 VALUES(to_date('2012-03-08', 'YYYY-MM-DD'));
INSERT INTO T146 VALUES(to_date('2012-03-09', 'YYYY-MM-DD'));

实测结果：

[zhangqin12356]

with tb as
(select to_date('2012-03-06', 'YYYY-MM-DD') t_date from dual union all
select to_date('2012-03-07', 'YYYY-MM-DD') from dual union all
select to_date('2012-03-08', 'YYYY-MM-DD') from dual union all
select to_date('2012-03-09', 'YYYY-MM-DD') from dual)

select sdate from
(select date'2012-03-03'+rownum as sdate from dual
connect by rownum < date'2012-03-11'-date'2012-03-03')
where sdate not in
(select t_date from tb)


学习了

[xpingping]

先构造time开始到结束区间里的日期
然后与现有的日期做minus

with tb as
(select '2012-3-6' time from dual union all
select '2012-3-7' from dual union all
select '2012-3-8' from dual union all
select '2012-3-9' from dual)
--查询
(select to_date('2012-3-3', 'yyyy-mm-dd')+rownum from dual 
connect by rownum<(select to_date('2012-3-11', 'yyyy-mm-dd')-to_date('2012-3-3', 'yyyy-mm-dd')from dual))
minus
(select to_date(time,'yyyy-mm-dd')from tb)

[大明湖畔_帅锅]

少量数据 应该没什么效率问题了

CREATE TABLE tb1
(
    t_date DATE
);
INSERT INTO tb1 VALUES(to_date('2012-03-06', 'YYYY-MM-DD'));
INSERT INTO tb1 VALUES(to_date('2012-03-07', 'YYYY-MM-DD'));
INSERT INTO tb1 VALUES(to_date('2012-03-08', 'YYYY-MM-DD'));
INSERT INTO tb1 VALUES(to_date('2012-03-09', 'YYYY-MM-DD'));

select sdate from 
(select date'2012-03-03'+rownum as sdate from dual
        connect by rownum < date'2012-03-11'-date'2012-03-03')
where sdate not in 
(select t_date from tb1)

          sdate 
------------------------
1	2012/3/4
2	2012/3/5
3	2012/3/10

[lf_can]

with tb as
(select to_date('2012-03-06', 'YYYY-MM-DD') t_date from dual union all
select to_date('2012-03-07', 'YYYY-MM-DD') from dual union all
select to_date('2012-03-08', 'YYYY-MM-DD') from dual union all
select to_date('2012-03-09', 'YYYY-MM-DD') from dual
)
SELECT B.SDATE FROM
(select date'2012-03-03'+rownum -1 as sdate from dual
connect by rownum <= date'2012-03-11'- date'2012-03-03'+1) B
WHERE NOT EXISTS (
SELECT 1 FROM tb WHERE tb.t_date = B.sdate
)