一个表test:
username,id
a,1
a,2
a,3
b,1
b,3
b,4
c,1
c,2
c,3
c,5
d,2
d,3
d,4
d,5要查出username对应的ID不连续的username。
如上面的结果是:
b
c我的思路是分组username,然后每组的max(id)-min(id),得到的值如果不等于每组记录数count(id)-1,那么就这个值就是需要查出的。
或者有没有别的思路,是否能用一条SQL语句搞定?请高手!!!
username,id
a,1
a,2
a,3
b,1
b,3
b,4
c,1
c,2
c,3
c,5
d,2
d,3
d,4
d,5要查出username对应的ID不连续的username。
如上面的结果是:
b
c我的思路是分组username,然后每组的max(id)-min(id),得到的值如果不等于每组记录数count(id)-1,那么就这个值就是需要查出的。
或者有没有别的思路,是否能用一条SQL语句搞定?请高手!!!
create table test(username varchar(10),id int)
insert into test select 'a',1
insert into test select 'a',2
insert into test select 'a',3
insert into test select 'b',1
insert into test select 'b',3
insert into test select 'b',4
insert into test select 'c',1
insert into test select 'c',2
insert into test select 'c',3
insert into test select 'c',5
insert into test select 'd',2
insert into test select 'd',3
insert into test select 'd',4
insert into test select 'd',5
go
select username from test a group by username having count(*)<max(id)-min(id)+1
/*
username
----------
b
c(2 行受影响)
*/
go
drop table test
go
--> -->
if not object_id(N'Tempdb..#test') is null
drop table #test
Go
Create table #test([username] nvarchar(1),[id] int)
Insert #test
select N'a',1 union all
select N'a',2 union all
select N'a',3 union all
select N'b',1 union all
select N'b',3 union all
select N'b',4 union all
select N'c',1 union all
select N'c',2 union all
select N'c',3 union all
select N'c',5 union all
select N'd',2 union all
select N'd',3 union all
select N'd',4 union all
select N'd',5
Go
Select [username]
from #test AS a
WHERE NOT EXISTS(SELECT 1 FROM #test WHERE [username]=a.[username] AND ID=a.ID+1)
GROUP BY [username]
HAVING COUNT(1)>1/*
b
c
*/
username
from
test a left join test b
on
a.[username]=b.[username]
where
a.id=b.id-1
and
exists(select 1 from test where username=a.username and id<>a.id)
and
a.username is not null
drop table test
go
create table test
(
username varchar(10),
id int
)
go
insert into test
select 'a',1 union all
select 'a',2 union all
select 'a',3 union all
select 'b',1 union all
select 'b',3 union all
select 'b',4 union all
select 'c',1 union all
select 'c',2 union all
select 'c',3 union all
select 'c',5 union all
select 'd',2 union all
select 'd',3 union all
select 'd',4 union all
select 'd',5
go
select username from test group by username having max(id)-min(id)<>count(*)-1
go
/*
username
----------
b
c(2 行受影响)
*/