sqlserver/sql语句

[zrl凝望遗失]

这里面的字段值是可重复的（上面是用分组查询出的没重复的结果），但是没有连续，我想找到缺少了哪些数字，用纯sql实现的，函数的已经有了，谢谢各位了

[唐诗三百首]

try this,

select a.number 'qzh'
 from master.dbo.spt_values a 
 left join (select qzh from [表名] group by qzh) b 
 on a.type='P' and a.number>0 and a.number=b.qzh
 where b.qzh is null

[SqlServer2008]

declare @table table
(
  id int
)

insert into @table(id) 
select 1 union all
select 2 union all
select 3 union all
select 4 union all
select 6 union all
select 8 union all
select 9 union all
select 10 union all
select 11 union all
select 13 union all
select 14 union all
select 15 union all
select 17 union all
select 18 

--查出缺少的数字
select number from master.dbo.spt_values where type='p' and number!=0 and number<=(select max(id) from @table) and number not in(select id from @table)

[Leon_He2014]

;with t as
(
select 1 id
union all select 2 
union all select 3 
union all select 4 
union all select 5 
union all select 6 
union all select 9 
union all select 11 
union all select 12
)
select a.number
from master..spt_values a
left join t on a.number=t.id
where a.type='p' and t.id is null
and a.number between (select min(id) from t) and (select max(id) from t)

[zrl凝望遗失]

引用 1 楼 ap0405140 的回复:

try this,

select a.number 'qzh'
 from master.dbo.spt_values a 
 left join (select qzh from [表名] group by qzh) b 
 on a.type='P' and a.number>0 and a.number=b.qzh
 where b.qzh is null

这样查出来的不对，谢谢了哟

[zrl凝望遗失]

引用 2 楼 sqlserver2008 的回复:

declare @table table
(
  id int
)

insert into @table(id) 
select 1 union all
select 2 union all
select 3 union all
select 4 union all
select 6 union all
select 8 union all
select 9 union all
select 10 union all
select 11 union all
select 13 union all
select 14 union all
select 15 union all
select 17 union all
select 18 

--查出缺少的数字
select number from master.dbo.spt_values where type='p' and number!=0 and number<=(select max(id) from @table) and number not in(select id from @table)

谢谢哟，正解，结贴，由于是新手分很少莫嫌弃哟