3.2w+
社区成员
- 主页
- 基础类
- 应用实例
- 新技术前沿
我在表中有一单号字段，怎样显示出中间没有用的单号？请进来看详细内容。谢谢!
我有一单号字段：如下
单号
WL0001
WL0002
WL0004
RP0001
RP0002
RP0003
RP0006
其中 单号列少了WL0003 RP0004 RP0005
这样就表示销售人员漏单，我怎么样找出漏的这些单？非常感谢!
单号
WL0001
WL0002
WL0004
RP0001
RP0002
RP0003
RP0006
其中 单号列少了WL0003 RP0004 RP0005
这样就表示销售人员漏单，我怎么样找出漏的这些单？非常感谢!
...全文
请发表友善的回复…
发表回复
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 单号
不过这样找出来是部分，不全。
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 单号
不过这样找出来是部分，不全。
zjcxc--个人微信公共号同名 2003-12-16
--测试表
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 行）
--*/
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 行）
--*/
zjcxc--个人微信公共号同名 2003-12-16
--函数再改一下:
--得到编号缺号的字符列表
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,@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--个人微信公共号同名 2003-12-16
--得到编号缺号的字符列表
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
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
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 单号
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 单号
select * from table
where not exists (select 1 from table t where abs(right(t.单号)-table.单号)=1)
order by 单号