1.你说的间断是以多长的时间为间断呢? 2.重复的记录: select b.* from 表1 b where b.time=(select a.time from 表1 a where a.time=b.time and a.id<>b.id)
--创建测试环境 create table # ( ID int, [time] varchar(20), ccd1 int,ccd2 int ) insert # select 23,'14:12:12',34,35 union select 24,'14:12:13',45,67 union select 25,'14:24:18',54,56 union select 26,'14:24:19',33,34 union select 27,'14:24:20',34,54 union select 28,'14:24:21',33,43 union select 28,'14:24:21',33,44-- 测试 select [间断点]=max(ID) from # group by left(time,5) select * from # A where exists(select 1 from # where [time]=A.[time] group by [time] having count(1)>1) --删除测试环境 drop table #--结果 /*间断点 ----------- 24 28(所影响的行数为 2 行)ID time ccd1 ccd2 ----------- -------------------- ----------- ----------- 28 14:24:21 33 43 28 14:24:21 33 44(所影响的行数为 2 行) */
2.重复的记录:
select b.* from 表1 b where b.time=(select a.time from 表1 a where a.time=b.time and a.id<>b.id)
create table #
(
ID int,
[time] varchar(20),
ccd1 int,ccd2 int
)
insert #
select 23,'14:12:12',34,35 union
select 24,'14:12:13',45,67 union
select 25,'14:24:18',54,56 union
select 26,'14:24:19',33,34 union
select 27,'14:24:20',34,54 union
select 28,'14:24:21',33,43 union
select 28,'14:24:21',33,44-- 测试
select [间断点]=max(ID) from # group by left(time,5)
select * from # A where exists(select 1 from # where [time]=A.[time] group by [time] having count(1)>1)
--删除测试环境
drop table #--结果
/*间断点
-----------
24
28(所影响的行数为 2 行)ID time ccd1 ccd2
----------- -------------------- ----------- -----------
28 14:24:21 33 43
28 14:24:21 33 44(所影响的行数为 2 行)
*/