在数据表内,插入如下测试数据,请写T-SQL语句查询出不连续的数字
create table test
( id int primary key )
go
insert into test values (1 )
insert into test values (2 )
insert into test values (3 )
insert into test values (4 )
insert into test values (5 )
insert into test values (6 )
insert into test values (8 )
insert into test values (9 )
insert into test values (11)
insert into test values (12)
insert into test values (13)
insert into test values (14)
insert into test values (18)
insert into test values (19)
go
结果:6,8,9,11,14,18,19
create table test
( id int primary key )
go
insert into test values (1 )
insert into test values (2 )
insert into test values (3 )
insert into test values (4 )
insert into test values (5 )
insert into test values (6 )
insert into test values (8 )
insert into test values (9 )
insert into test values (11)
insert into test values (12)
insert into test values (13)
insert into test values (14)
insert into test values (18)
insert into test values (19)
go
结果:6,8,9,11,14,18,19
学习
insert into test values (8 )
insert into test values (9 )
insert into test values (11)
这没连起 6 8 9 11
select @sql=isnull(@sql+',','')+ltrim(a.id) from test a
left join test b on a.id=b.id-1
left join test c on a.id=c.id+1
where b.id is null or c.id is null
and a.id!=1
select @sql--结果:
-------------------
6,8,9,11,14,18,19