解决方案 »
- ◆用decode如何实现Case when功能◆
- POSTGRE移植ORACLE问题
- PL/SQL Developer 7.0.3 大家过来帮帮忙
- ORACLE:绝对值函数问题
- 请问,oracle中如何区分有序表和无序表?
- xp上安装Oracle出错!信息是ORA-00988:missing or invalid password(s)
- like %%在PL/SQL怎么用?特别是like一个变量的时候
- 在oracle的ops结构中,如何指定连接的实例
- oracle里面有没有十进制到十六进制和十六进制到十进制函数
- char 和 varchar2 有什么区别?
- 为什么创建的job仅仅执行了一次?
- 求一大数据量SQl
SELECT 1 ID,'2014-03-12' sd,'2013-03-22' ed FROM dual UNION ALL
SELECT 2 ID,'2014-03-07' sd,NULL ed FROM dual UNION ALL
SELECT 3 ID,'2014-03-09' sd,NULL ed FROM dual UNION ALL
SELECT 4 ID,'2014-03-05' sd,NULL ed FROM dual UNION ALL
SELECT 5 ID,'2014-03-25' sd,'2014-03-30' ed FROM dual
), test1 AS(
SELECT 1 ID,'2014-03-03' sd,'2013-03-04' ed FROM dual UNION ALL
SELECT 2 ID,'2014-03-23' sd,NULL ed FROM dual UNION ALL
SELECT 3 ID,'2014-03-09' sd,NULL ed FROM dual UNION ALL
SELECT 4 ID,'2014-03-05' sd,NULL ed FROM dual UNION ALL
SELECT 4 ID,'2014-03-03' sd,'2014-03-06' ed FROM dual UNION ALL
SELECT 5 ID, '2014-03-25' SD, '2014-03-30' ED
FROM DUAL)
SELECT *
FROM TEST1 T
WHERE NOT (EXISTS (SELECT 1
FROM TEST S
WHERE S.SD = T.SD
AND S.ED IS NULL) AND t.ed IS NULL)
AND NOT (EXISTS (SELECT 1
FROM TEST S
WHERE ((S.SD <= T.SD AND decode(S.ED,NULL,'9999-12-31',s.ed) >= T.SD)
OR (S.SD <= T.ED AND decode(S.ED,NULL,'9999-12-31',s.ed) >= T.ED))
AND t.ed IS NOT NULL))
我试验了几个值,应该没问题,你按照你的业务自己验证一下,不满足的举个例子
SELECT 1 ID,'2014-03-12' sd,'2013-03-22' ed FROM dual UNION ALL
SELECT 2 ID,'2014-03-07' sd,NULL ed FROM dual UNION ALL
SELECT 3 ID,'2014-03-09' sd,NULL ed FROM dual UNION ALL
SELECT 4 ID,'2014-03-05' sd,NULL ed FROM dual UNION ALL
SELECT 5 ID,'2014-03-25' sd,'2014-03-30' ed FROM dual
), test1 AS(
SELECT 1 ID,'2014-03-03' sd,'2013-03-04' ed FROM dual UNION ALL
SELECT 2 ID,'2014-03-23' sd,NULL ed FROM dual UNION ALL
SELECT 3 ID,'2014-03-09' sd,NULL ed FROM dual UNION ALL
SELECT 4 ID,'2014-03-05' sd,NULL ed FROM dual UNION ALL
SELECT 4 ID,'2014-03-03' sd,'2014-03-06' ed FROM dual UNION ALL
SELECT 5 ID, '2014-03-25' SD, '2014-03-30' ED
FROM DUAL)
SELECT *
FROM TEST1 T
WHERE NOT (EXISTS (SELECT 1
FROM TEST S
WHERE S.SD = T.SD
AND S.ED IS NULL) AND t.ed IS NULL)
AND NOT (EXISTS (SELECT 1
FROM TEST S
WHERE ((S.SD <= T.SD AND decode(S.ED,NULL,'9999-12-31',s.ed) >= T.SD)
OR (S.SD <= T.ED AND decode(S.ED,NULL,'9999-12-31',s.ed) >= T.ED))
AND t.ed IS NOT NULL))
我试验了几个值,应该没问题,你按照你的业务自己验证一下,不满足的举个例子
还是不能满足 比如我test1加上个SELECT 6 ID,'2014-03-26' sd,NULL ed FROM dual UNION ALL这个应该是不允许的 因为26号在'2014-03-25' SD, '2014-03-30' 之间
您的查询结果和我想要的是相反的 SQL执行出来是得到的是时间无交叉的结果 我想要是的是如果时间无交叉就不显示 如果有交叉就显示test中存在交叉的记录和id test1中我是固定只查一个 而且只传日期不传id的
SELECT 1 ID,'2014-03-12' sd,'2013-03-22' ed FROM dual UNION ALL
SELECT 2 ID,'2014-03-07' sd,NULL ed FROM dual UNION ALL
SELECT 3 ID,'2014-03-09' sd,NULL ed FROM dual UNION ALL
SELECT 4 ID,'2014-03-05' sd,NULL ed FROM dual UNION ALL
SELECT 5 ID,'2014-03-25' sd,'2014-03-30' ed FROM dual
), test1 AS(
SELECT 1 ID,'2014-03-03' sd,'2013-03-04' ed FROM dual UNION ALL
SELECT 2 ID,'2014-03-23' sd,NULL ed FROM dual UNION ALL
SELECT 3 ID,'2014-03-09' sd,NULL ed FROM dual UNION ALL
SELECT 4 ID,'2014-03-05' sd,NULL ed FROM dual UNION ALL
SELECT 4 ID,'2014-03-03' sd,'2014-03-06' ed FROM dual UNION ALL
SELECT 5 ID, '2014-03-25' SD, '2014-03-30' ED
FROM DUAL)
SELECT *
FROM TEST1 T
WHERE NOT (EXISTS (SELECT 1
FROM TEST S
WHERE S.SD = T.SD
AND S.ED IS NULL) AND t.ed IS NULL)
AND NOT (EXISTS (SELECT 1
FROM TEST S
WHERE ((S.SD <= T.SD AND decode(S.ED,NULL,'9999-12-31',s.ed) >= T.SD)
OR (S.SD <= T.ED AND decode(S.ED,NULL,'9999-12-31',s.ed) >= T.ED))
AND t.ed IS NOT NULL))
我试验了几个值,应该没问题,你按照你的业务自己验证一下,不满足的举个例子
还是不能满足 比如我test1加上个SELECT 6 ID,'2014-03-26' sd,NULL ed FROM dual UNION ALL这个应该是不允许的 因为26号在'2014-03-25' SD, '2014-03-30' 之间
您的查询结果和我想要的是相反的 SQL执行出来是得到的是时间无交叉的结果 我想要是的是如果时间无交叉就不显示 如果有交叉就显示test中存在交叉的记录和id test1中我是固定只查一个 而且只传日期不传id的
这个是给你写的一个校验的sql,校验你需要插入的值到底可以不可以。
按你说的,再加个判断条件就可以了,如下:
WITH test AS(SELECT 1 ID,'2014-03-12' sd,'2013-03-22' ed FROM dual UNION ALL
SELECT 2 ID,'2014-03-07' sd,NULL ed FROM dual UNION ALL
SELECT 3 ID,'2014-03-09' sd,NULL ed FROM dual UNION ALL
SELECT 4 ID,'2014-03-05' sd,NULL ed FROM dual UNION ALL
SELECT 5 ID,'2014-03-25' sd,'2014-03-30' ed FROM dual),
test1 AS(SELECT 1 ID,'2014-03-03' sd,'2013-03-04' ed FROM dual UNION ALL
SELECT 2 ID,'2014-03-23' sd,NULL ed FROM dual UNION ALL
SELECT 3 ID,'2014-03-09' sd,NULL ed FROM dual UNION ALL
SELECT 4 ID,'2014-03-05' sd,NULL ed FROM dual UNION ALL
SELECT 4 ID,'2014-03-03' sd,'2014-03-06' ed FROM dual UNION ALL
SELECT 5 ID, '2014-03-25' SD, '2014-03-30' ED FROM DUAL UNION ALL
SELECT 6 ID,'2014-03-26' sd,NULL ed FROM dual)
SELECT *
FROM TEST1 T
WHERE NOT (EXISTS (SELECT 1
FROM TEST S
WHERE S.SD = T.SD
AND S.ED IS NULL) AND T.ED IS NULL)
AND NOT (EXISTS (SELECT 1
FROM TEST S
WHERE S.SD <= T.SD AND s.ed>=t.sd
AND S.ED IS NOT NULL) AND T.ED IS NULL)
AND NOT (EXISTS (SELECT 1
FROM TEST S
WHERE ((S.SD <= T.SD AND
DECODE(S.ED, NULL, '9999-12-31', S.ED) >= T.SD) OR
(S.SD <= T.ED AND
DECODE(S.ED, NULL, '9999-12-31', S.ED) >= T.ED))
AND T.ED IS NOT NULL))
你不是需要检验时候可以插入么,通过sql判断一下就可以了啊。。