我在表中有一单号字段，怎样显示出中间没有用的单号？请进来看详细内容。谢谢!

[Angelnet]

我有一单号字段：如下
单号
WL0001
WL0002
WL0004
RP0001
RP0002
RP0003
RP0006

其中 单号列少了WL0003 RP0004 RP0005
这样就表示销售人员漏单，我怎么样找出漏的这些单？非常感谢!

[realgz]

给区间可以吧？
select * from table
where not exists (select 1 from table t where abs(right(t.单号)-table.单号)=1)
order by 单号

[realgz]

select * from table
where not exists (select 1 from table t where abs(right(t.单号,4)-right(table.单号,4))=1)
order by 单号

[victorycyz]

declare @m int

create table #t (id,int)
select @m=max(cast(right(单号,4) as int)) from tablename
while @m>0
begin
insert into #t values (@m)
@m=@m-1
end

select a.id
from #t a left join
tablename b
on a.id=cast(right(单号,4) as int)
where b.单号 is null

drop table #t

[zjcxc]

--得到编号缺号的字符列表
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_getNseries]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_getNseries]
GO

create function f_getNseries(@head varchar(10))
returns varchar(8000)
as
begin
declare @re varchar(8000),@id int
select @re='',@id=0,@head=@head+'%'
select @re=case @id when id-1 then @re
else @re+','+cast(@id+1 as varchar)
+case @id+1 when id-1 then ''
else '-'+cast(id-1 as varchar) end
end
,@id=id
from (select id=cast(right(单号,4) as int) from 表 where 单号 like @head) a
set @re=substring(@re,2,8000)
return(@re)
end
go

--调用实现你的要求
select dbo.f_getNseries(aa) from(
select distinct aa=left(单号,2) from 表
) a

[zjcxc]

--函数再改一下:
--得到编号缺号的字符列表
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_getNseries]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_getNseries]
GO

create function f_getNseries(@head varchar(10))
returns varchar(8000)
as
begin
declare @re varchar(8000),@id int,@h varchar(10)
select @re='',@id=0,@h=@head+'%'
select @re=case @id when id-1 then @re
else @re+','+@head+right('0000'+cast(@id+1 as varchar),4)
+case @id+1 when id-1 then ''
else '-'+@head+right('0000'+cast(id-1 as varchar),4) end
end
,@id=id
from (select id=cast(right(单号,4) as int) from 表 where 单号 like @h) a
set @re=substring(@re,2,8000)
return(@re)
end
go

[zjcxc]

--测试表
create table 表(单号 varchar(10))
insert into 表
select 'WL0001'
union all select 'WL0002'
union all select 'WL0004'
union all select 'RP0001'
union all select 'RP0002'
union all select 'RP0003'
union all select 'RP0006'
go

--得到编号缺号的字符列表
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_getNseries]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_getNseries]
GO

create function f_getNseries(@head varchar(10))
returns varchar(8000)
as
begin
declare @re varchar(8000),@id int,@h varchar(10)
select @re='',@id=0,@h=@head+'%'
select @re=case @id when id-1 then @re
else @re+','+@head+right('0000'+cast(@id+1 as varchar),4)
+case @id+1 when id-1 then ''
else '-'+@head+right('0000'+cast(id-1 as varchar),4) end
end
,@id=id
from (select id=cast(right(单号,4) as int) from 表 where 单号 like @h) a
set @re=substring(@re,2,8000)
return(@re)
end
go

--调用实现你的要求
select dbo.f_getNseries(aa) from(
select distinct aa=left(单号,2) from 表
) a

go
drop table 表

/*--测试结果
-------------------
RP0004-RP0005
WL0003

（所影响的行数为 2 行）

--*/

[gmlxf]

要想找出WL0003 RP0004 RP0005这样的号码难度有点吧。

select left(单号,2) + cast((right(a.单号,4)-1) as varchar) 单号 from table a
where not exists (select 1 from table where right(单号,4)=right(a.单号,4)-1)
order by 单号

不过这样找出来是部分，不全。

[Angelnet]

zjcxc(邹建) 我发觉你太恐怖了，我去试试，谢谢!
也谢谢大家!