自增列查询问题

Question

5.4.2 查询缺号分布情况的示例.sql--测试数据
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
--*/

Answer 1

CREATE TABLE tb(col1  int)--创建测试表及数据
INSERT tb SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 6
UNION ALL SELECT 7
UNION ALL SELECT 10
GO
create table #TMP(id int) --创建一个临时表declare @max_a int,@min_a int,@i int
 select @max_a=max(col1),@min_a=min(col1) from tb
set @i=@min_a
while @i<@max_a 
begin
  insert into #TMP(ID) VALUES(@I)
  set @i=@i+1
end
SELECT * FROM #TMP WHERE ID NOT IN(SELECT * FROM tb)-----查询结果------id          
----------- 
4
5
8
9（所影响的行数为 4 行）

Answer 2

编号 姓名
1    张山
3    历史
4    王无
6    李强
     桥三
     大兵我想对没有编号的进行编号的自动处理，但是已经有编号的不要进行修改。
如果发现有断号的就将号码分配给没有编号的。
结果如下：
编号 姓名
1   张山
3   历史
4   王无
6   李强
2   桥三
5   大兵
遍历记录，如果id为空，则找出最小未使用id，然后update找最小未使用id如下：
select (case when exists (select 1 from test where id=1)
         then min(id+1) else 1 end)
from test
where id not in(select id-1 from test)CREATE TABLE [bhb] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[bh] [int] NULL 
) ON [PRIMARY]Declare @intI int
Set @intI = 1
Declare @intChdI int
While @intI < 500   --根据实际需要来确定循环次数
Begin
 if not EXISTS(select bh from twhere bh=@intI)
  begin
    Insert Into bhb(bh) Values (@intI)
  end
   Set @intI= @intI + 1
End
*/Declare @intI int
set @intI=1
update t set bh=(select bh from bhb where id=@intI),@intI=@intI+1 where bh is nulldrop table bhbwhile exists (select 1 from tablename where 编号 is null)
begin
   set rowcount 1
   update tablename
      set 编号=(select min(编号)+1 from tablename a where 编号 is not null 
           and not exists (select 1 from tablename where 编号=a.编号+1)
           )
   where 编号 is null
   set rowcount 0
end--检查结果
select * from tablename
order by 编号
---------------------------
题目:
现在数据库由于需要要自己定义唯一编号,唯一编号由3个数字组成。这三个数字我存储为nvarchar.
由于唯一编号在使用中不可能超过999条记录，所以3个足够用。但是碰到一个问题.第一次使用时添加记录可以001,002,003...这样顺序添加.
当对这些记录进行删除之后再次添加.要核对输入的记录是否重复。这样非常被动。
每次添加记录都要验证该编号是否存在。设计这样的数据库，实在是没辙.
现在有没有办法能够自己得出一个在999内的未使用的编号呢。
非常感谢。用存储过程亦可。我有想过遍历,但是效率不高。希望有更简洁的方式。--1、没有数据的情况
if object_id('pubs..t') is not null
   drop table t
gocreate table t(id char(3))select (case when exists (select 1 from t where cast(id as int)=1)
             then right('00' + rtrim(cast(min(id + 1) as char(3))) , 3) else '001' end)
       as 最小数据
from t
where id not in(select id - 1 from t)drop table t--结果
最小数据  
----- 
001（所影响的行数为 1 行）
--2、有数据且连号。
if object_id('pubs..t') is not null
   drop table t
gocreate table t(id char(3))insert into t(id) values('001')
insert into t(id) values('002')
insert into t(id) values('003')select (case when exists (select 1 from t where cast(id as int)=1)
             then right('00' + rtrim(cast(min(id + 1) as char(3))) , 3) else '001' end)
       as 最小数据
from t
where id not in(select id - 1 from t)drop table t--结果
最小数据  
----- 
004（所影响的行数为 1 行）
--3、有数据但断号
if object_id('pubs..t') is not null
   drop table t
gocreate table t(id char(3))insert into t(id) values('001')
insert into t(id) values('002')
insert into t(id) values('004')select (case when exists (select 1 from t where cast(id as int)=1)
             then right('00' + rtrim(cast(min(id + 1) as char(3))) , 3) else '001' end)
       as 最小数据
from t
where id not in(select id - 1 from t)drop table t--结果
最小数据  
----- 
003（所影响的行数为 1 行）怎么查出有丢失的数据大家好
问一个SQL的问题了我有一个表名为：table里面有以下数据：
peono    data                  count
001      2007-01-01 08:00:10     1
001      2007-01-01 08:10:10     2
001      2007-01-01 08:20:10     3
001      2007-01-01 08:30:10     4
002      2007-01-01 08:35:10     1
001      2007-01-01 08:40:10     5
002      2007-01-01 08:45:10     2
002      2007-01-01 08:47:10     3 
                                  002   2007-01-01 08:48:10   4 (此行丢失)
002      2007-01-01 08:55:10     5
....................
怎么查出在2007-01-01----2007-01-03 这段时间内有丢失count的工号
（出现1  3没有count为2的那一条则算丢失2，
  出现3  5没有count为4的那一条则算丢失4）
我想要得出的结果：在以上table查询
peono
002
怎么查出啊？请帮忙，急急！！！！！！！！
/*
create table tb070207001(peono varchar(3),data datetime,count int)
insert into tb070207001
select '001','2007-01-01 08:00:10',     1
union all select '001','2007-01-01 08:10:10',     2
union all select '001','2007-01-01 08:20:10',     3
union all select '001','2007-01-01 08:30:10',     4
union all select '002','2007-01-01 08:35:10',     1
union all select '001','2007-01-01 08:40:10',     5
union all select '002','2007-01-01 08:45:10',     2
union all select '002','2007-01-01 08:47:10',     3                                   
union all select '002','2007-01-01 08:55:10',     5
*/
select x.peono from (
select a.peono from tb070207001 a
where exists(select * from tb070207001 b  where b.count-1=a.count and b.peono=a.peono)
or exists(select * from tb070207001 c  where c.count+1=a.count and c.peono=a.peono)
)x group by peono having count(x.peono)<(select count(peono) from tb070207001 where peono=x.peono group by peono)
peono 
----- 
002（所影响的行数为 1 行）

Answer 3

zswuhaihong 
方法可行
有没有更好的呢

Answer 4

 
  WITH
  L0   AS(SELECT 1 AS c UNION ALL SELECT 1),
  L1   AS(SELECT 1 AS c FROM L0 AS A, L0 AS B),
  L2   AS(SELECT 1 AS c FROM L1 AS A, L1 AS B),
  L3   AS(SELECT 1 AS c FROM L2 AS A, L2 AS B),
  L4   AS(SELECT 1 AS c FROM L3 AS A, L3 AS B),
  L5   AS(SELECT 1 AS c FROM L4 AS A, L4 AS B),
  Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY c) AS n FROM L5)
  SELECT n FROM Nums  left join tt on Nums.n=tt.id
where tt.id is null

Answer 5

WITH
  L0   AS(SELECT 1 AS c UNION ALL SELECT 1),
  L1   AS(SELECT 1 AS c FROM L0 AS A, L0 AS B),
  L2   AS(SELECT 1 AS c FROM L1 AS A, L1 AS B),
  L3   AS(SELECT 1 AS c FROM L2 AS A, L2 AS B),
  L4   AS(SELECT 1 AS c FROM L3 AS A, L3 AS B),
  L5   AS(SELECT 1 AS c FROM L4 AS A, L4 AS B),
  Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY c) AS n FROM L5)
  SELECT n FROM Nums  left join tt on Nums.n=tt.id 
where tt.id is null and n<=(select max(id) from tt)

Answer 6

--借用临时表：
create table tb(id int)
insert into tb select 1
insert into tb select 2
insert into tb select 3
insert into tb select 5
insert into tb select 6
insert into tb select 7
insert into tb select 9
insert into tb select 11
insert into tb select 12
declare @sql varchar(1000)
select @sql='select top '+cast(max(id) as varchar)+' bh=identity(int,1,1) into ## from syscolumns' from tb
exec(@sql)
select bh from ## where not exists(select 1 from tb where bh=id)

Answer 7

基本和2楼的思路一样，只是不用临时表
create table TB (
  COL1 int)--创建测试表及数据insert TB
select 2
union all
select 3
union all
select 6
union all
select 7
union all
select 10godeclare  @max_a int,
         @min_a int,
         @i     int
                --这里分开写，效率比较高select @max_a = max(COL1)
from   TBselect @min_a = min(COL1)
from   TBselect @i = @min_a
            --感觉不使用临时表也可以实现，起码节省了写磁盘的IO时间while @i < @max_a
  begin
    if not exists (select 1
                   from   TB
                   where  COL1 = @i)
      print @i
    select @i = @i + 1
  end

Answer 8

11楼的貌似有些局限性如果子增列的最大数值大于syscolumns表的个数，就不能正常得到结果了。