如何查漏编号字段

如何查漏编号字段

SQL code

我有一张表A,有字段A(编号，插入一行编号+1)，字段B（金额），字段C（人次）

现在想统计一段数据：select count(1) from 表A where 字段A>=200 and 字段A<=400
结果是199张，漏了一个编号，想用一个查询语句查出漏的编号是那个号？或者漏的编号的上一个编号

--参考
SQL code SELECT Col1+1 AS 缺号起点,
          (SELECT MIN(Col1) FROM tb
WHERE Col1>t.Col1) -1 AS 缺号终点
 FROM tb AS t
 WHERE NOT EXISTS
(SELECT * FROM tb WHERE Col1=t.Col1+1)

假设只有三位数字。
那么可以用如下代码:

SQL codeSELECT 字段1
from LI T
where not exists (select 1 From LI
                  where (Cast(right(字段1,3) as int)  -  cast(right(T.字段1,3) as int))  = 1)

SQL code--生成已用编号分布字符串的函数   
CREATE FUNCTION f_GetStrSeries(@col1 varchar(10))  
RETURNS varchar(8000)  
AS  
BEGIN  
    DECLARE @re varchar(8000),@pid int  
    SELECT @re='',@pid=-1  
    SELECT @re=CASE   
            WHEN col2=@pid+1 THEN @re  
            ELSE @re  
                +CASE   
                    WHEN RIGHT(@re,CHARINDEX(',',REVERSE(@re)+',')-1)=@pid THEN ''  
                    ELSE CAST(-@pid as varchar)  
                END  
                +','+CAST(col2 as varchar)   
            END,  
        @pid=col2  
    FROM tb  
    WHERE col1=@col1  
    ORDER BY col2  
    RETURN(STUFF(@re,1,2,'')  
        +CASE   
            WHEN RIGHT(@re,CHARINDEX(',',REVERSE(@re)+',')-1)=@pid THEN ''  
            ELSE CAST(-@pid as varchar)  
        END)  
END  
GO  
  
--生成缺号分布字符串的函数   
CREATE FUNCTION f_GetStrNSeries(@col1 varchar(10))  
RETURNS VARCHAR(8000)  
AS  
BEGIN  
    DECLARE @re varchar(8000),@pid int  
    SELECT @re='',@pid=0  
    SELECT @re=CASE   
            WHEN col2=@pid+1 THEN @re  
            ELSE @re+','+CAST(@pid+1 as varchar)  
                +CASE   
                    WHEN @pid+1=col2-1 THEN ''  
                    ELSE CAST(1-col2 as varchar)  
                END  
            END,  
        @pid=col2  
    FROM tb  
    WHERE col1=@col1  
    ORDER BY col2  
    RETURN(STUFF(@re,1,1,''))  
END  
GO  
  
--调用测试   
--测试数据   
CREATE TABLE tb(col1 varchar(10),col2 int)  
INSERT tb SELECT 'a',2  
UNION ALL SELECT 'a',3  
UNION ALL SELECT 'a',5  
UNION ALL SELECT 'a',8  
UNION ALL SELECT 'a',9  
UNION ALL SELECT 'b',1  
UNION ALL SELECT 'b',5  
UNION ALL SELECT 'b',6  
UNION ALL SELECT 'b',7  
  
--查询   
SELECT col1,  
    col2_Series=dbo.f_GetStrSeries(col1),  
    col2_Series=dbo.f_GetStrNSeries(col1)  
FROM tb  
GROUP BY col1  
/*--结果   
col1       col2_Series       col2_Series   
-------------- ------------------------ --------------   
a          2-3,5,8-9        1,4,6-7  
b          1,5-7           2-4  
--*/  

--生成已用编号分布字符串的函数
CREATE FUNCTION f_GetStrSeries(@col1 varchar(10))
RETURNS varchar(8000)
AS
BEGIN
    DECLARE @re varchar(8000),@pid int
    SELECT @re='',@pid=-1
    SELECT @re=CASE 
            WHEN col2=@pid+1 THEN @re
            ELSE @re
                +CASE 
                    WHEN RIGHT(@re,CHARINDEX(',',REVERSE(@re)+',')-1)=@pid THEN ''
                    ELSE CAST(-@pid as varchar)
                END
                +','+CAST(col2 as varchar) 
            END,
        @pid=col2
    FROM tb
    WHERE col1=@col1
    ORDER BY col2
    RETURN(STUFF(@re,1,2,'')
        +CASE 
            WHEN RIGHT(@re,CHARINDEX(',',REVERSE(@re)+',')-1)=@pid THEN ''
            ELSE CAST(-@pid as varchar)
        END)
END
GO
 
--生成缺号分布字符串的函数
CREATE FUNCTION f_GetStrNSeries(@col1 varchar(10))
RETURNS VARCHAR(8000)
AS
BEGIN
    DECLARE @re varchar(8000),@pid int
    SELECT @re='',@pid=0
    SELECT @re=CASE 
            WHEN col2=@pid+1 THEN @re
            ELSE @re+','+CAST(@pid+1 as varchar)
                +CASE 
                    WHEN @pid+1=col2-1 THEN ''
                    ELSE CAST(1-col2 as varchar)
                END
            END,
        @pid=col2
    FROM tb
    WHERE col1=@col1
    ORDER BY col2
    RETURN(STUFF(@re,1,1,''))
END
GO
 
--调用测试
--测试数据
CREATE TABLE tb(col1 varchar(10),col2 int)
INSERT tb SELECT 'a',2
UNION ALL SELECT 'a',3
UNION ALL SELECT 'a',5
UNION ALL SELECT 'a',8
UNION ALL SELECT 'a',9
UNION ALL SELECT 'b',1
UNION ALL SELECT 'b',5
UNION ALL SELECT 'b',6
UNION ALL SELECT 'b',7
 
--查询
SELECT col1,
    col2_Series=dbo.f_GetStrSeries(col1),
    col2_Series=dbo.f_GetStrNSeries(col1)
FROM tb
GROUP BY col1
/*--结果
col1       col2_Series       col2_Series 
-------------- ------------------------ --------------
a          2-3,5,8-9        1,4,6-7
b          1,5-7           2-4
--*/