22,210
社区成员
发帖
与我相关
我的任务
分享
declare @tb table (col varchar(1000))
insert into @tb (col) values('1,2,3,4,5,7,8,9,10,11,13,14,15,20')
--select * from @tb
--col
--1,2,3,4,5,7,8,9,10,11,13,14,15,20
;with tb as (
SELECT row_number()OVER( ORDER BY col)rn,col=convert(int,SUBSTRING(a.col,b.number,CHARINDEX(',',a.col+',',b.number)-b.number) )
FROM @tb a,master..spt_values b
WHERE b.number<=LEN(a.col+'a')
AND CHARINDEX(',',','+a.col,b.number)=b.number
AND b.type = 'P'
)
SELECT b.number from tb t right join master..spt_values b on t.col = b.number
,(select min(col) [min],max(col) [max] from tb) c
where b.type = 'P' and t.col is null and b.number between c.[min] and c.[max]
--只统计给定值最小值和最大值之间的值不连续值,不能统计超过2047的值
--若要统计大约2047的值需要创建一个连续整数表带起系统表master..spt_values,最大值根据统计范围确定。
/*
number
6
12
16
17
18
19
*/
declare @表A table (字段1 int)
insert into @表A
select 1 union all
select 2 union all
select 3 union all
select 4 union all
select 5 union all
select 7 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
declare @表b table(字段2 int)
declare @t int
select @t= min(字段1) from @表A
while (@t<(select max(字段1) from @表A))
begin
insert into @表b select @t
set @t=@t+1
end
select b.* from @表b b left join @表A a
on b.字段2=a.字段1 where a.字段1 is null
/*
字段2
-----------
6
12
*/