select * from table1 as a where not exists(select 1 from table1 where UserName=a.UserName and StartTime<a.Endtime and a.StartTime<Endtime and ID<>a.ID)
select begindate,enddate from tableName group by begindate,enddate having COUNT(1)>1
select * from tb a where exists(select 1 from tb where code=a.code and (stym between a.stym and a.enym or enym between a.stym and a.enym))
你还差个标记列. create table tb(code varchar(10),stym varchar(10),enym varchar(10)) insert into tb select '606952636','198703','198712' insert into tb select '606952636','198801','198812' insert into tb select '606952636','198811','198912' insert into tb select '606952636','199001','199012' insert into tb select '606952636','199101','199112' insert into tb select '606952636','199201','199212' go ;with cte as( select *,rn=row_number()over(partition by code order by stym) from tb ) select * from cte a where exists(select 1 from cte where code=a.code and rn<>a.rn and (stym between a.stym and a.enym or enym between a.stym and a.enym)) /* code stym enym rn ---------- ---------- ---------- -------------------- 606952636 198801 198812 2 606952636 198811 198912 3(2 行受影响)*/ go drop table tb
select begindate,enddate
from tableName
group by begindate,enddate
having COUNT(1)>1
字段: code stym enym
数据: 606952636 198703 198712
606952636 198801 198812
606952636 198811 198912
606952636 199001 199012
606952636 199101 199112
606952636 199201 199212则第二条数据有重复,重复的是:198811 198812
问题:怎样通过sql判断重复?
create table tb(code varchar(10),stym varchar(10),enym varchar(10))
insert into tb select '606952636','198703','198712'
insert into tb select '606952636','198801','198812'
insert into tb select '606952636','198811','198912'
insert into tb select '606952636','199001','199012'
insert into tb select '606952636','199101','199112'
insert into tb select '606952636','199201','199212'
go
;with cte as(
select *,rn=row_number()over(partition by code order by stym) from tb
)
select * from cte a
where exists(select 1 from cte where code=a.code and rn<>a.rn and (stym between a.stym and a.enym or enym between a.stym and a.enym))
/*
code stym enym rn
---------- ---------- ---------- --------------------
606952636 198801 198812 2
606952636 198811 198912 3(2 行受影响)*/
go
drop table tb