假设表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点没弄出来-_-#!...
------解决方案--------------------
没太看明白。
就2、3点
if not exist (select * from tb where id= 'A001 ')
print 'A001 '
B001,C001,D001类似
希望对你有帮助
------解决方案--------------------
有办法,就是杂建立一个表,当你删除一个号的时候,就在这个表建立一条记录,
插入的时候,先找这个表的记录,如果没有,再找最大记录
------解决方案--------------------
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) 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
*/
------解决方案--------------------
CREATE TABLE T1(COL VARCHAR(10))
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 [email protected]+RIGHT(1000+ID,3)
SELECT @S=MIN(COL) FROM @T A WHERE NOT EXISTS
(SELECT 1 FROM T1 WHERE LEFT(COL,1)[email protected] 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
C004==========> 应该是C001才对啊
D001
*/
------解决方案--------------------
Debug:
--------------------------------------------------
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