假设表tb(id varchar(4)),id可重复。
1.有1(001)的记录
a(不连续)."A001","A001","A003"->取得"A002"
b(连续的)."B001","B002"->取得"B003"
2.没有1的记录
"C003"->取得"C001"
3.没有的记录
假设"Dxxx"是不存在的->取得"D001"现在要得到ABCD各类的最小空号就是"A002","B003","C001","D001"请大虾指教,第2点没弄出来-_-#!...
1.有1(001)的记录
a(不连续)."A001","A001","A003"->取得"A002"
b(连续的)."B001","B002"->取得"B003"
2.没有1的记录
"C003"->取得"C001"
3.没有的记录
假设"Dxxx"是不存在的->取得"D001"现在要得到ABCD各类的最小空号就是"A002","B003","C001","D001"请大虾指教,第2点没弄出来-_-#!...
就2、3点
if not exist (select * from tb where id='A001')
print 'A001'
B001,C001,D001类似希望对你有帮助
有记录如下
"A001"
"A001"
"A003"
"B001"
"B002"
"C003"要返回结果集
"A002"
"B003"
"C001"
"D001"--因为不存在所以为"001"
insert into @t select 'A001'
insert into @t select 'A001'
insert into @t select 'A003'
insert into @t select 'B001'
insert into @t select 'B002'
insert into @t select 'C003'select
a.m+right('000'+rtrim(min(a.n)+1),3) as newCode
from
(select left(code,1) as m,cast(right(code,3) as int) n from @t) a
where
not exists(select 1 from @t where left(code,1)=a.m and right(code,3)=a.n+1)
group by
a.m
union
select char(ascii(max(left(code,1)))+1)+'001' from @t
/*
newCode
--------
A002
B003
C004
D001
*/
之前我也参考过你的sql,不过调试不通过...
现在这个可以了,谢了:)。
INSERT INTO T1 VALUES('A001')
INSERT INTO T1 VALUES('A001')
INSERT INTO T1 VALUES('A003')
INSERT INTO T1 VALUES('B001')
INSERT INTO T1 VALUES('B002')
INSERT INTO T1 VALUES('C003')
GO
CREATE PROCEDURE P(@COL CHAR(1))
AS
DECLARE @S VARCHAR(10)
DECLARE @T TABLE(ID INT IDENTITY(1,1),COL VARCHAR(10))
INSERT INTO @T
SELECT TOP 999 0 FROM SYSCOLUMNS A,SYSCOLUMNS B
UPDATE @T SET COL=@COL+RIGHT(1000+ID,3)
SELECT @S=MIN(COL) FROM @T A WHERE NOT EXISTS
(SELECT 1 FROM T1 WHERE LEFT(COL,1)=@COL AND COL=A.COL)
SELECT @S
GO
EXEC P 'A'
EXEC P 'B'
EXEC P 'C'
EXEC P 'D'
EXEC P 'F'
DROP TABLE T1
DROP PROCEDURE P
看错了一点,
/*
newCode
--------
A002
B003
C001<--才对啊
D001
*/
newCode
--------
A002
B003
C004==========>应该是C001才对啊
D001
*/
你是对的:)
可否写一段直接执行的sql,就像libin_ftsafe(子陌红尘) 的sql那样,只用一句话呢?
--------------------------------------------------
declare @t table(code varchar(6))
insert into @t select 'A001'
insert into @t select 'A001'
insert into @t select 'A003'
insert into @t select 'B001'
insert into @t select 'B002'
insert into @t select 'C003'select
a.m+right('000'+rtrim(min(a.n)+1),3) as newCode
from
(select left(code,1) as m,cast(right(code,3) as int) n from @t
union
select left(code,1),'000' from @t) a
where
not exists(select 1 from @t where left(code,1)=a.m and right(code,3)=a.n+1)
group by
a.m
union
select char(ascii(max(left(code,1)))+1)+'001' from @t/*
newCode
--------
A002
B003
C001
D001
*/
insert tb values('A001')
insert tb values('A003')
insert tb values('B001')insert tb values('B002')
declare @c char(1) , @cID varchar(4)select @c = 'A'
if not exists(select 1 from tb where [id]=@c+'001')
begin
select @cID=@c+'001'
end
else
begin
select @cID= min([id]) from tb where left([id],1)=@c and cast(right([id],3) as int)+1 not in (select cast(right([id],3) as int) from tb where left([id],1)=@c)
select @cID = @c + right('000'+ltrim(rtrim(cast(cast(right(@cID,3) as int)+1 as char(3)))) ,3)
endselect @c,@cID
drop table tb
A01/001 A01/002 A01/003
A02/001 A02/002 A02/003
A03/001 A03/002 A03/003
B01/001 B01/002 B01/003
B02/001 B02/002 B02/003
as declare @mPID char(4),
@mFilter char(2),
@nTmpID int
set @mFilter = @inStr+'%'
select ID=Identity(int,1,1),a.* into #tmp
from (select distinct Col from t1 where COl like+@mFilter)a if exists(select top 1 * from #tmp)
begin
select top 1 @nTmpID=ID from #tmp where ID<>Convert(int,right(Col,3))
if @@rowCount=0
select @nTmpID=max(ID)+1 from #tmp
if @nTmpID<10
set @mPID = @inStr+'00'+Convert(Char(1),@nTmpID)
else if @nTmpID<100
set @mPID = @inStr+'0'+Convert(Char(1),@nTmpID)
else if @nTmpID<1000
set @mPID = @inStr+Convert(Char(1),@nTmpID)
end
else
set @mPID = @inStr+'001' drop table #tmp
select @mPIDGo--exec GetNextID 'A'
--exec GetNextID 'B'
--exec GetNextID 'C'
--exec GetNextID 'D'
union
select char(ascii(max(left(code,1)))+1)+'001' from @t
确实输出了D001这个值,但只适用于帖子中这个例子,因为他的语句会直接产生一条某类001的结果,该类字母比原数据中出现的最大字母大1。
如果数据中C类中没有数据,而D类中有D001,那么他的语句执行结果会有以下错误:
1. 没有C001的输出
2. 出现了E001输出,而E类是不存在的。
1. 首先得有一个专门的表指出你所有的类名,因为这个题目要求未出现的项,即集合的补集,那就先得给出全集。例如是A B C D, 还是A B C D E F, 或者根本没有规律如A E G T X Y。假设这个表名是CLASSNAME。
2. 想办法把你的三种情况变成一种,这样就可用一条SQL语句来产生输出。办法很简单,在你的原数据中加入所有类的000纪录,实际上,这是一种修改边界条件的方法,这样一来,只有一种情况了,那就是查找最小的未使用编号了。语句如下(假设你的数据表名是CLASSDATA)
select left(code,1) as m,cast(right(code,3) as int) n from CLASSDATA
union
select CODE,0 from CLASSNAME
3. 对上面语句产生的表做查询,找出每类中后面没有连续值的所有编号中最小的那个,具体写法可参见libin_ftsafe(子陌红尘)回帖的语句。
declare @t table(code varchar(6))
insert into @t select 'A001'
insert into @t select 'A001'
insert into @t select 'A003'
insert into @t select 'B001'
insert into @t select 'B002'
insert into @t select 'D003'declare @s table(code varchar(6))
insert into @s
select 'A' union all
select 'B' union all
select 'C' union all
select 'D' union all
select 'F' select
a.m+right(rtrim(min(a.n)+1001),3) as newCode
from
(select left(code,1) as m,cast(right(code,3) as int) n from @t
union
select code,0 from @s) a LEFT JOIN @t B on left(B.code,1)=a.m and right(B.code,3)=a.n+1
where B.code is null
group by
a.m
drop function dbo.numToStr
go
create function dbo.numToStr(@num int)
returns char(3)
begin
declare @str char(3)
declare @sNum varchar(3)
set @sNum=cast(@num as varchar)
if len(@sNum)=1
set @str = '00'+@sNum
else if len(@sNum)=2
set @str = '0'+@sNum
else
set @str = @sNum
return @str
end
go
drop function dbo.strToNum
go
create function dbo.strToNum(@str char(3))
returns int
begin
declare @num int
if substring(@str,1,2)='00'
set @num=cast(substring(@str,3,1) as int)
else if substring(@str,1,1)='0'
set @num=cast(substring(@str,2,2) as int)
else
set @num=cast(@str as int)
return @num
end
go
------函数------select
case
when min(dbo.strToNum(substring(id,2,3)))<>1 then (select left(id,1)+'001' from konghao as c where left(c.id,1)=left(konghao.id,1))
else
(select top 1 left(a.id,1)+dbo.numToStr((dbo.strToNum(substring(a.id,2,3))+1)) from konghao as a where left(a.id,1)=left(konghao.id,1) and not exists(select 1 from konghao as b where left(b.id,1)=left(a.id,1) and b.id=left(b.id,1)+dbo.numToStr(dbo.strToNum(substring(a.id,2,3))+1)))
end
as maxUnUsedIDfrom konghao group by left(id,1)