导航
  • 主页
  • 基础类
  • 应用实例
  • 新技术前沿

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

Angelnet 2003-12-16 05:12:33
我有一单号字段:如下
单号
WL0001
WL0002
WL0004
RP0001
RP0002
RP0003
RP0006

其中 单号列少了WL0003 RP0004 RP0005
这样就表示销售人员漏单,我怎么样找出漏的这些单?非常感谢!
...全文
8 点赞 收藏 8
写回复
8 条回复
切换为时间正序
请发表友善的回复…
发表回复
Angelnet 2003-12-16
zjcxc(邹建) 我发觉你太恐怖了,我去试试,谢谢!
也谢谢大家!
回复
gmlxf 2003-12-16
要想找出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 单号

不过这样找出来是部分,不全。
回复
--测试表
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 行)

--*/
回复
--函数再改一下:
--得到编号缺号的字符列表
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
回复
--得到编号缺号的字符列表
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
回复
victorycyz 2003-12-16
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
回复
realgz 2003-12-16
select * from table
where not exists (select 1 from table t where abs(right(t.单号,4)-right(table.单号,4))=1)
order by 单号
回复
realgz 2003-12-16
给区间可以吧?
select * from table
where not exists (select 1 from table t where abs(right(t.单号)-table.单号)=1)
order by 单号
回复
发动态
发帖子
MS-SQL Server
创建于2007-09-28

3.2w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
社区公告
暂无公告