这个问题有点难
Tab A 中有以下column ESD,LSD分别为日期值
ID ESD LSD
eg : 1 2007-06-01 2007-06-08
2 2007-05-27 2007-06-04
3 ' ' ' '
4 2007-06-25 2007-06-12
5 ' ' ' '
分析:
ESD LSD
|___________________________________| ( A )
2007/06/01 2006/06/08
------- 4 days---
ESD LSD
|_________________________| ( B )
2007/05/27 2007/06/04
-------4 days----
ESD LSD
|__________________________| ( C )
2007/06/05 2007/06/12
如图所示
其中如果以A为参照对象
B 为A的左区间
C 为A的右区间
B,C 同时都 满足： 与A有一个交叉的区间，且区间为 4days
现在的问题就是
如何通过 sql 神奇语句 找出所有 有相同区间的数据
我头有点大了 , 谁能帮我 ???!!
在线侯教
[解决办法]
CREATE TABLE TabA(ID INT IDENTITY(1,1),ESD SMALLDATETIME,LSD SMALLDATETIME)
INSERT INTO TabA
SELECT '2007-06-01 ', '2007-06-08 ' UNION ALL
SELECT '2007-05-27 ', '2007-06-04 ' UNION ALL
SELECT '2007-06-01 ', '2007-06-04 ' UNION ALL
SELECT '2007-06-05 ', '2007-06-12 ' UNION ALL
SELECT '2007-06-05 ', '2007-06-08 '
--DROP TABLE TabA
SELECT '左区间 ',* FROM TabA A LEFT OUTER JOIN TabA B ON DATEADD(DAY ,4,B.LSD) = A.LSD-- OR DATEADD(DAY ,-4,B.ESD) = A.ESD
WHERE A.ID = 1
UNION ALL
SELECT '右区间 ',* FROM TabA A LEFT OUTER JOIN TabA B ON DATEADD(DAY ,-4,B.ESD) = A.ESD
WHERE A.ID = 1
[解决办法]
SELECT '左区间 ',* FROM TabA A LEFT OUTER JOIN TabA B ON DATEADD(DAY ,4,B.LSD) = A.LSD
WHERE A.ID = 1
UNION ALL
SELECT '右区间 ',* FROM TabA A LEFT OUTER JOIN TabA B ON DATEADD(DAY ,-4,B.ESD) = A.ESD
WHERE A.ID = 1
[解决办法]
/*以下语句稍作调整,结果中就会出现你所说的同时符合左右区间条件的结果集,这时你可以再加过滤条件来区别到底是优先左区间还是右区间*/
CREATE TABLE TabA(ID INT IDENTITY(1,1),ESD SMALLDATETIME,LSD SMALLDATETIME)
INSERT INTO TabA
SELECT '2007-06-01 ', '2007-06-10 ' UNION ALL
SELECT '2007-06-05 ', '2007-06-06 ' UNION ALL
SELECT '2007-05-31 ', '2007-06-06 ' UNION ALL
SELECT '2007-06-05 ', '2007-06-12 ' UNION ALL
SELECT '2007-06-05 ', '2007-06-08 '
--DROP TABLE TabA
SELECT '左区间 ',* FROM TabA A LEFT OUTER JOIN TabA B ON DATEADD(DAY ,4,B.LSD) = A.LSD
WHERE A.ID = 1
UNION ALL
SELECT '右区间 ',* FROM TabA A LEFT OUTER JOIN TabA B ON DATEADD(DAY ,-4,B.ESD) = A.ESD
WHERE A.ID = 1
[解决办法]
CREATE TABLE TabA(ID INT IDENTITY(1,1),ESD SMALLDATETIME,LSD SMALLDATETIME)
INSERT INTO TabA
SELECT '2007-04-20 ', '2007-04-27 ' UNION ALL
SELECT '2007-04-20 ', '2007-04-27 ' UNION ALL
SELECT '2007-04-27 ', '2007-05-04 ' UNION ALL
SELECT '2007-04-23 ', '2007-04-30 ' UNION ALL
SELECT '2007-03-25 ', '2007-04-01 ' UNION ALL
SELECT '2007-03-25 ', '2007-04-01 ' UNION ALL
SELECT '2007-03-25 ', '2007-04-01 '
--DROP TABLE TabA
SELECT '左区间 ',* FROM TabA A LEFT OUTER JOIN TabA B ON DATEADD(DAY ,4,B.ESD) = A.ESD OR DATEADD(DAY ,4,B.LSD) = A.LSD
--WHERE A.ID = 3
UNION ALL
SELECT '右区间 ',* FROM TabA A LEFT OUTER JOIN TabA B ON DATEADD(DAY ,-4,B.ESD) = A.ESD OR DATEADD(DAY ,-4,B.LSD) = A.LSD
--WHERE A.ID = 3
-----------------------------------------------
结果集:(表明第3条的最区间包含4,或4的有区间包含3.只有这两条成立,其中的语句你可以自己调整试一下,应该和你表述的意思差不多了)
左区间
12007-04-20 00:00:002007-04-27 00:00:00NULLNULLNULL左区间
22007-04-20 00:00:002007-04-27 00:00:00NULLNULLNULL左区间
32007-04-27 00:00:002007-05-04 00:00:0042007-04-23 00:00:002007-04-30 00:00:00左区间
42007-04-23 00:00:002007-04-30 00:00:00NULLNULLNULL左区间
52007-03-25 00:00:002007-04-01 00:00:00NULLNULLNULL左区间
62007-03-25 00:00:002007-04-01 00:00:00NULLNULLNULL左区间
72007-03-25 00:00:002007-04-01 00:00:00NULLNULLNULL右区间
12007-04-20 00:00:002007-04-27 00:00:00NULLNULLNULL右区间
22007-04-20 00:00:002007-04-27 00:00:00NULLNULLNULL右区间
32007-04-27 00:00:002007-05-04 00:00:00NULLNULLNULL右区间
42007-04-23 00:00:002007-04-30 00:00:0032007-04-27 00:00:002007-05-04 00:00:00右区间
52007-03-25 00:00:002007-04-01 00:00:00NULLNULLNULL右区间
62007-03-25 00:00:002007-04-01 00:00:00NULLNULLNULL右区间
72007-03-25 00:00:002007-04-01 00:00:00NULLNULLNULL
[解决办法]
CREATE TABLE TabA(ID INT IDENTITY(1,1),ESD SMALLDATETIME,LSD SMALLDATETIME)
INSERT INTO TabA
SELECT '2007-04-20 ', '2007-04-27 ' UNION ALL
SELECT '2007-04-20 ', '2007-04-27 ' UNION ALL
SELECT '2007-04-27 ', '2007-05-04 ' UNION ALL
SELECT '2007-04-23 ', '2007-04-30 ' UNION ALL
SELECT '2007-03-25 ', '2007-04-01 ' UNION ALL
SELECT '2007-03-25 ', '2007-04-01 ' UNION ALL
SELECT '2007-03-25 ', '2007-04-01 '
--DROP TABLE TabA
SELECT '左区间 ',* FROM TabA A LEFT OUTER JOIN TabA B ON DATEADD(DAY ,4,B.ESD) = A.ESD OR DATEADD(DAY ,4,B.LSD) = A.LSD
--WHERE A.ID = 3
UNION ALL
SELECT '右区间 ',* FROM TabA A LEFT OUTER JOIN TabA B ON DATEADD(DAY ,-4,B.ESD) = A.ESD OR DATEADD(DAY ,-4,B.LSD) = A.LSD
--WHERE A.ID = 3
-----------------------------------------------
结果集:(表明第3条的最区间包含4,或4的有区间包含3.只有这两条成立,其中的语句你可以自己调整试一下,应该和你表述的意思差不多了)
左区间12007-04-20 00:00:002007-04-27 00:00:00NULLNULLNULL
左区间22007-04-20 00:00:002007-04-27 00:00:00NULLNULLNULL
左区间32007-04-27 00:00:002007-05-04 00:00:0042007-04-23 00:00:002007-04-30 00:00:00
左区间42007-04-23 00:00:002007-04-30 00:00:00NULLNULLNULL
左区间52007-03-25 00:00:002007-04-01 00:00:00NULLNULLNULL
左区间62007-03-25 00:00:002007-04-01 00:00:00NULLNULLNULL
左区间72007-03-25 00:00:002007-04-01 00:00:00NULLNULLNULL
右区间12007-04-20 00:00:002007-04-27 00:00:00NULLNULLNULL
右区间22007-04-20 00:00:002007-04-27 00:00:00NULLNULLNULL
右区间32007-04-27 00:00:002007-05-04 00:00:00NULLNULLNULL
右区间42007-04-23 00:00:002007-04-30 00:00:0032007-04-27 00:00:002007-05-04 00:00:00
右区间52007-03-25 00:00:002007-04-01 00:00:00NULLNULLNULL
右区间62007-03-25 00:00:002007-04-01 00:00:00NULLNULLNULL
右区间72007-03-25 00:00:002007-04-01 00:00:00NULLNULLNULL
[解决办法]
CREATE TABLE TabA(ID INT IDENTITY(1,1),ESD SMALLDATETIME,LSD SMALLDATETIME)
INSERT INTO TabA
SELECT '2007-04-20 ', '2007-04-27 ' UNION ALL
SELECT '2007-04-20 ', '2007-04-27 ' UNION ALL
SELECT '2007-04-27 ', '2007-05-04 ' UNION ALL
SELECT '2007-04-23 ', '2007-04-30 ' UNION ALL
SELECT '2007-03-25 ', '2007-04-01 ' UNION ALL
SELECT '2007-03-25 ', '2007-04-01 ' UNION ALL
SELECT '2007-03-25 ', '2007-04-01 '
--DROP TABLE TabA
SELECT '左区间 ',* FROM TabA A LEFT OUTER JOIN TabA B ON DATEADD(DAY ,4,B.ESD) = A.ESD OR DATEADD(DAY ,4,B.LSD) = A.LSD
UNION ALL
SELECT '右区间 ',* FROM TabA A LEFT OUTER JOIN TabA B ON DATEADD(DAY ,-4,B.ESD) = A.ESD OR DATEADD(DAY ,-4,B.LSD) = A.LSD
-------------------------------------------
/*
其实这两个满足条件的记录是重复的， 因为它们有相同的一对ID,只需要保留一个就可以了
----我显示出来的是一个全结果集，也就是包含或满足左区间或满足右区间的结果集，你只需要按你需要的内容再加过滤条件就可以了
---------------------------
另外我有些不明白 WHERE A.ID = 3 //这里为什么要指定这个特定的条件呢?
---- 只是为了说明ID为3的记录包含的符合条件的记录
*/
