求tsql: 如何查询出日期连续性有问题的所有行？

Question

求tsql: 如何查询出日期连续性有问题的所有行？
问题详述：
全表按order by num,start后，在同一num组内，上一行的时间end和下一行的时间start必须是接续的，说两行接续，也就是说"下行start=上行end+1"，如end=2008-3-3且start=2008-3-4就是接续的；每当发现“不”接续的日期，就把发生不接续的上行和下行放进结果表中。
下面举例说明。
如下表
id    num    start        end
1     1      2008-1-1     2008-3-3
2     1      2008-3-4     2008-5-12
3     1      2008-5-10    2008-6-20
4     1      2008-6-28    2008-8-15
5     2      2009-2-14    2008-4-23
6     2      2009-2-14    2008-7-23
7     2      2009-7-24    2008-10-24
8     2      2009-7-28    2008-11-24
结果应是
id    num    start        end
2     1      2008-3-4     2008-5-12
3     1      2008-5-10    2008-6-20
4     1      2008-6-28    2008-8-15
5     2      2009-2-14    2008-4-23
6     2      2009-2-14    2008-7-23
7     2      2009-7-24    2008-10-24
8     2      2009-7-28    2008-11-20
下表和上表几乎一模一样，只是少了原来的第一行即：
1     1      2008-1-1     2008-3-3
因这行未被牵连在“不连续”中，也就是说这行的时间end和下行的时间start正好差1天，是连续的。

Answer 1

本帖最后由 libin_ftsafe 于 2009-02-22 14:29:03 编辑

  

Answer 2

declare @t table(id int,num int,start datetime,[end] datetime)
insert into @t select 1,1,'2008-01-01','2008-03-03' 
insert into @t select 2,1,'2008-03-04','2008-05-12' 
insert into @t select 3,1,'2008-05-10','2008-06-20' 
insert into @t select 4,1,'2008-06-28','2008-08-15' 
insert into @t select 5,2,'2009-02-14','2008-04-23' 
insert into @t select 6,2,'2009-02-14','2008-07-23' 
insert into @t select 7,2,'2009-07-24','2008-10-24' 
insert into @t select 8,2,'2009-07-28','2008-11-24' select 
    t.* 
from
    @t t
where
    not exists(select 1 from @t where num=t.num and start=t.[end]+1)/*
id          num         start                                                  end                                                    
----------- ----------- ------------------------------------------------------ ------------------------------------------------------ 
2           1           2008-03-04 00:00:00.000                                2008-05-12 00:00:00.000
3           1           2008-05-10 00:00:00.000                                2008-06-20 00:00:00.000
4           1           2008-06-28 00:00:00.000                                2008-08-15 00:00:00.000
5           2           2009-02-14 00:00:00.000                                2008-04-23 00:00:00.000
6           2           2009-02-14 00:00:00.000                                2008-07-23 00:00:00.000
7           2           2009-07-24 00:00:00.000                                2008-10-24 00:00:00.000
8           2           2009-07-28 00:00:00.000                                2008-11-24 00:00:00.000
*/

Answer 3

create table tb(id int,num int,start datetime,[end] datetime) 
insert into tb select 1,1,'2008-1-1','2008-3-3' 
insert into tb select 2,1,'2008-3-4','2008-5-12' 
insert into tb select 3,1,'2008-5-10','2008-6-20' 
insert into tb select 4,1,'2008-6-28','2008-8-15' 
insert into tb select 5,2,'2009-2-14','2008-4-23' 
insert into tb select 6,2,'2009-2-14','2008-7-23' 
insert into tb select 7,2,'2009-7-24','2008-10-24' 
insert into tb select 8,2,'2009-7-28','2008-11-24'
select * from tb a where not exists(select 1 from tb where datediff(dd,start,a.[end])=-1)
go
drop table tb
/*
id          num         start                   end
----------- ----------- ----------------------- -----------------------
2           1           2008-03-04 00:00:00.000 2008-05-12 00:00:00.000
3           1           2008-05-10 00:00:00.000 2008-06-20 00:00:00.000
4           1           2008-06-28 00:00:00.000 2008-08-15 00:00:00.000
5           2           2009-02-14 00:00:00.000 2008-04-23 00:00:00.000
6           2           2009-02-14 00:00:00.000 2008-07-23 00:00:00.000
7           2           2009-07-24 00:00:00.000 2008-10-24 00:00:00.000
8           2           2009-07-28 00:00:00.000 2008-11-24 00:00:00.000
*/

Answer 4

hmm, tsql是神奇的，但两位更神奇！

Answer 5

楼上各位独有一个小毛病，就是最后一行肯定没有下行，可能不应该选出来，逻辑(可能可以优化)应该是select 
    t.* 
from
    表 t
where
    not exists(select 1 from 表 where num=t.num and start=t.end+1) 
    and exists(select 1 from 表 where num=t.num and end>t.end)
union
select 
    t.* 
from
    表 t
where
    not exists(select 1 from 表 where num=t.num and start=t.end-1) 
    and exists(select 1 from 表 where num=t.num and end<t.end)
    

Answer 6

建议用这样的数据测试id    num    start        end 
1    1      2008-1-1    2008-3-3       --无上行，下行正常，不选中
2    1      2008-3-4    2008-5-12      --上行正常，下行不正常，选中
3    1      2008-5-10    2008-6-20     --上行不正常，下行正常，选中
4    1      2008-6-21    2008-8-15     --上行正常，下行无，不选中
5    2      2009-2-14    2008-4-23     -- num=2的无所谓了
6    2      2009-2-14    2008-7-23 
7    2      2009-7-24    2008-10-24 
8    2      2009-7-28    2008-11-24 

Answer 7

7楼写的有问题，更正测试如下declare @t table(id int,num int,start datetime,[end] datetime)
insert into @t select 1,1,'2008-01-01','2008-03-03' 
insert into @t select 2,1,'2008-03-04','2008-05-12' 
insert into @t select 3,1,'2008-05-10','2008-06-20' 
insert into @t select 4,1,'2008-06-21','2008-08-15' 
insert into @t select 5,2,'2009-02-14','2008-04-23' 
insert into @t select 6,2,'2009-02-14','2008-07-23' 
insert into @t select 7,2,'2009-07-24','2008-10-24' 
insert into @t select 8,2,'2009-07-28','2008-11-24' select 
    t.* 
from
    @t t
where
    not exists(select 1 from @t where num=t.num and start=t.[end]+1) 
    and exists(select 1 from @t where num=t.num and [end]>t.[end])
union
select 
    t.* 
from
    @t t
where
    not exists(select 1 from @t where num=t.num and [end]=t.[start]-1) 
    and exists(select 1 from @t where num=t.num and [start]<t.[start])--结果
id          num         start                                                  end                                                    
----------- ----------- ------------------------------------------------------ ------------------------------------------------------ 
2           1           2008-03-04 00:00:00.000                                2008-05-12 00:00:00.000
3           1           2008-05-10 00:00:00.000                                2008-06-20 00:00:00.000
5           2           2009-02-14 00:00:00.000                                2008-04-23 00:00:00.000
6           2           2009-02-14 00:00:00.000                                2008-07-23 00:00:00.000
7           2           2009-07-24 00:00:00.000                                2008-10-24 00:00:00.000
8           2           2009-07-28 00:00:00.000                                2008-11-24 00:00:00.000（所影响的行数为 6 行）

Answer 8

以上回复我感觉很“像”，但是我仍存一丝怀疑：
我觉得正确的选法应当是，选出所有找不到上行(start日期最小的行除外)，或找不到下行(日期end最大的除外)的行放入结果集。我怀疑以上回复都没有考虑完全吧？
如果我说错了，是因为我的理解能力有限，请耐心解释明白。

Answer 9

Frankly，楼主所说的日期连续，自然应该包括该记录与之前记录之间连续连续，以及该记录与之后记录之间连续；如果按照这样的思路，楼主给出的例子中，第二条记录因为与第一条记录存在连续的关系，也应该在结果集中被筛选掉。declare @t table(id int,num int,start datetime,[end] datetime)
insert into @t select 1,1,'2008-01-01','2008-03-03' 
insert into @t select 2,1,'2008-03-04','2008-05-12' 
insert into @t select 3,1,'2008-05-10','2008-06-20' 
insert into @t select 4,1,'2008-06-28','2008-08-15' 
insert into @t select 5,2,'2009-02-14','2008-04-23' 
insert into @t select 6,2,'2009-02-14','2008-07-23' 
insert into @t select 7,2,'2009-07-24','2008-10-24' 
insert into @t select 8,2,'2009-07-28','2008-11-24' select 
    t.* 
from
    @t t
where
    not exists(select 1 from @t where num=t.num and start=t.[end]+1)
    and
    not exists(select 1 from @t where num=t.num and [end]=t.start-1)/*
id          num         start                                                  end                                                    
----------- ----------- ------------------------------------------------------ ------------------------------------------------------ 
3           1           2008-05-10 00:00:00.000                                2008-06-20 00:00:00.000
4           1           2008-06-28 00:00:00.000                                2008-08-15 00:00:00.000
5           2           2009-02-14 00:00:00.000                                2008-04-23 00:00:00.000
6           2           2009-02-14 00:00:00.000                                2008-07-23 00:00:00.000
7           2           2009-07-24 00:00:00.000                                2008-10-24 00:00:00.000
8           2           2009-07-28 00:00:00.000                                2008-11-24 00:00:00.000
*/

Answer 10

或者这么写declare @t table(id int,num int,start datetime,[end] datetime)
insert into @t select 1,1,'2008-01-01','2008-03-03' 
insert into @t select 2,1,'2008-03-04','2008-05-12' 
insert into @t select 3,1,'2008-05-10','2008-06-20' 
insert into @t select 4,1,'2008-06-21','2008-08-15' 
insert into @t select 5,2,'2009-02-14','2008-04-23' 
insert into @t select 6,2,'2009-02-14','2008-07-23' 
insert into @t select 7,2,'2009-07-24','2008-10-24' 
insert into @t select 8,2,'2009-07-28','2008-11-24' select 
    t.* 
from
    @t t
where
    not exists(select 1 from @t where num=t.num and start=t.[end]+1) 
    and exists(select 1 from @t where num=t.num and [end]>t.[end])
or
    not exists(select 1 from @t where num=t.num and [end]=t.[start]-1) 
    and exists(select 1 from @t where num=t.num and [start]<t.[start])--结果
id          num         start                                                  end                                                    
----------- ----------- ------------------------------------------------------ ------------------------------------------------------ 
2           1           2008-03-04 00:00:00.000                                2008-05-12 00:00:00.000
3           1           2008-05-10 00:00:00.000                                2008-06-20 00:00:00.000
5           2           2009-02-14 00:00:00.000                                2008-04-23 00:00:00.000
6           2           2009-02-14 00:00:00.000                                2008-07-23 00:00:00.000
7           2           2009-07-24 00:00:00.000                                2008-10-24 00:00:00.000
8           2           2009-07-28 00:00:00.000                                2008-11-24 00:00:00.000（所影响的行数为 6 行）

Answer 11

也许是题外话，我说说我的这个需求产生的背景。
我正接手做一个有线电视收费系统，它原来是用foxpro做的，我发现库中现有数据极为混乱，有同一时间段重叠收费的记录(当然是录多了，又没有设置有效性检查，不可能是用户交多了)，有交费时间不连续中间空档的。我求的这段tsql就是为了查清所有这些“不连续”，以便导入我新做的程序中。
有线电视的数据库结构很乱，受不了，尚没有一个清晰的思路。

Answer 12

不连续是指
insert into @t select 1,1,'2008-01-01','2008-03-03' 
insert into @t select 2,1,'2008-03-04','2008-05-12' 
insert into @t select 3,1,'2008-05-10','2008-06-20' 
insert into @t select 4,1,'2008-06-21','2008-08-15' 
任取两对中任取两个还是因为是连续的都不要呢？
有点乱

Answer 13

结果集中包含所有“不连续行”，满足以下条件的行就叫作“不连续行”：
(start为最小 and end不是最大 and end+1<>下一行的start) or (end为最大 and start-1<>上一行的end) or (start不是最小 and end不是最大 and (end+1<>下一行的start or start-1<>上一行的end))
这样说够清楚了没？呵呵

Answer 14

唉，觉得很无助！
每回我问到了答案，能判别答案的正确性，这回不行了。因为无法用有限的数据加以验证。请大家帮助看一下
请把我的条件和14楼的比较一下，两者是不是等效的？我复制自的条件如下：
(start为最小 and end不是最大 and end+1 <>下一行的start) or (end为最大 and start-1 <>上一行的end) or (start不是最小 and end不是最大 and (end+1 <>下一行的start or start-1 <>上一行的end)) 
我总觉得14楼的where字句是不是太简单了，存在漏洞？还是我的逻辑需要化简？

Answer 15

唉，觉得很无助！ 
每回我问到了答案，能判别答案的正确性，这回不行了。因为无法用有限的数据加以验证。 请大家帮助看一下 
请把我的条件和14楼的比较一下，两者是不是等效的？ 我复制自的条件如下： 
(start为最小 and end不是最大 and end+1 <>下一行的start) or (end为最大 and start-1 <>上一行的end) or (start不是最小 and end不是最大 and (end+1 <>下一行的start or start-1 <>上一行的end)) 
我总觉得14楼的where字句是不是太简单了，存在漏洞？还是我的逻辑需要化简？