Create Table TEST(ID Int, TestTime DateTime)
Insert TEST Select 1, '2007-03-29 07:30:00'
Union All Select 2, '2007-03-29 08:00:00'
Union All Select 3, '2007-03-30 07:00:00'
Union All Select 4, '2007-03-31 07:00:00'
Union All Select 5, '2007-03-31 07:50:00'
Union All Select 6, '2007-03-22 07:08:00'
Union All Select 7, '2007-03-23 08:00:00'
GO
Select * From TEST A
Where Not Exists(Select 1 From TEST Where DateDiff(dd, TestTime, A.TestTime) = 0 And
TestTime > A.TestTime And DatePart(Hour, TestTime) < 8)
And DatePart(Hour, TestTime) < 8
GO
------------------------------------------
假设表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点没弄出来-_-#!...
其实是这样的
有记录如下
"A001"
"A001"
"A003"
"B001"
"B002"
"C003"
要返回结果集
"A002"
"B003"
"C001"
"D001"--因为不存在所以为"001"
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
*/
----
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
select char(ascii(max(left(code,1)))+1)+'001' from @t
/*
newCode
--------
A002
B003
C001
D001
*/
-------------------------------------------------------------------
declare tb table ([id] varchar(4))(tb change into @tb)
insert tb values('A001')
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)
end
select @c,@cID
drop table tb
------------------
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 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