--测试数据 CREATE TABLE tb(col1 varchar(10),col2 int) INSERT tb SELECT 'a',2 UNION ALL SELECT 'a',3 UNION ALL SELECT 'a',6 UNION ALL SELECT 'a',7 UNION ALL SELECT 'a',8 UNION ALL SELECT 'b',1 UNION ALL SELECT 'b',5 UNION ALL SELECT 'b',6 UNION ALL SELECT 'b',7 GO--缺号分布查询 SELECT a.col1,start_col2=a.col2+1, end_col2=( SELECT MIN(col2) FROM tb aa WHERE col1=a.col1 AND col2>a.col2 AND NOT EXISTS( SELECT * FROM tb WHERE col1=aa.col1 AND col2=aa.col2-1)) -1 FROM( SELECT col1,col2 FROM tb UNION ALL --为每组编号补充查询起始编号是否缺号的辅助记录 SELECT DISTINCT col1,0 FROM tb )a,(SELECT col1,col2=MAX(col2) FROM tb GROUP BY col1)b WHERE a.col1=b.col1 AND a.col2<b.col2 --过滤掉每组数据中,编号最大的记录 AND NOT EXISTS( SELECT * FROM tb WHERE col1=a.col1 AND col2=a.col2+1) ORDER BY a.col1,start_col2 /*--结果 col1 start_col2 end_col2 -------------- -------------- ----------- a 1 1 a 4 5 b 2 4 --*/
select sn=identity(int),code into #t from tb; select top 1 replicate('0',4-len(sn))+cast(sn as varchar) from #t order by sn
忘加条件了: select top 1 replicate('0',4-len(sn))+cast(sn as varchar) from #t where sn<>cast(code as int) order by sn
declare @T table(code varchar(50)) insert @T values('0001') insert @T values('0002') insert @T values('0004') insert @T values('0005') insert @T values('0006') insert @T values('0007') insert @T values('0008') insert @T values('0010') insert @T values('0011')select top 1 right('0000'+rtrim(cast(code as int)+1),4) code from @T A where not exists (select * from @T B where cast(B.code as int)=cast(A.code as int)+1)/* code -------- 0003(1 行受影响) */
select top 1 right('0000'+rtrim(cast(code as int)+1),4) code from @T A where not exists (select * from @T B where cast(B.code as int)=cast(A.code as int)+1) order by code
CREATE TABLE tb(col1 varchar(10),col2 int)
INSERT tb SELECT 'a',2
UNION ALL SELECT 'a',3
UNION ALL SELECT 'a',6
UNION ALL SELECT 'a',7
UNION ALL SELECT 'a',8
UNION ALL SELECT 'b',1
UNION ALL SELECT 'b',5
UNION ALL SELECT 'b',6
UNION ALL SELECT 'b',7
GO--缺号分布查询
SELECT a.col1,start_col2=a.col2+1,
end_col2=(
SELECT MIN(col2) FROM tb aa
WHERE col1=a.col1 AND col2>a.col2
AND NOT EXISTS(
SELECT * FROM tb WHERE col1=aa.col1 AND col2=aa.col2-1))
-1
FROM(
SELECT col1,col2 FROM tb
UNION ALL --为每组编号补充查询起始编号是否缺号的辅助记录
SELECT DISTINCT col1,0 FROM tb
)a,(SELECT col1,col2=MAX(col2) FROM tb GROUP BY col1)b
WHERE a.col1=b.col1 AND a.col2<b.col2 --过滤掉每组数据中,编号最大的记录
AND NOT EXISTS(
SELECT * FROM tb WHERE col1=a.col1 AND col2=a.col2+1)
ORDER BY a.col1,start_col2
/*--结果
col1 start_col2 end_col2
-------------- -------------- -----------
a 1 1
a 4 5
b 2 4
--*/
select top 1 replicate('0',4-len(sn))+cast(sn as varchar) from #t order by sn
select top 1 replicate('0',4-len(sn))+cast(sn as varchar) from #t where sn<>cast(code as int) order by sn
insert @T values('0001')
insert @T values('0002')
insert @T values('0004')
insert @T values('0005')
insert @T values('0006')
insert @T values('0007')
insert @T values('0008')
insert @T values('0010')
insert @T values('0011')select top 1 right('0000'+rtrim(cast(code as int)+1),4) code
from @T A
where not exists
(select * from @T B where cast(B.code as int)=cast(A.code as int)+1)/*
code
--------
0003(1 行受影响)
*/
from @T A
where not exists
(select * from @T B where cast(B.code as int)=cast(A.code as int)+1)
order by code