一个SQL是否可以搞定？有点难。

[好记忆不如烂笔头abc]

一个表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语句搞定？请高手！！！

[-晴天]

有思路,而且是正确的,试着自己写写?

[-晴天]

忍不住手痒,写一个,楼主不要偷看哦!

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

[中国风]

use Tempdb
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
*/

[--小F--]

select 
  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

[快溜]

select username from test a group by username having count(*)<max(id)-min(id)+1

[pengxuan]

思路挺好， 这样写

if object_id('test','U') 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 行受影响)
*/

[好记忆不如烂笔头abc]

都是牛人哪，感谢！！！

[勿勿]

你的思路有了就要大胆的自己动手