当前位置: 代码迷 >> Sql Server >> 怎么查漏编号字段
  详细解决方案

怎么查漏编号字段

热度:1189   发布时间:2016-04-27 22:00:44
如何查漏编号字段
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 tbWHERE Col1>t.Col1) -1 AS 缺号终点 FROM tb AS t WHERE NOT EXISTS(SELECT * FROM tb WHERE Col1=t.Col1+1)
------解决方案--------------------
假设只有三位数字。
那么可以用如下代码:

SQL code
SELECT 字段1 from LI Twhere 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 [email protected]+1 THEN @re               ELSE @re                   +CASE                        WHEN RIGHT(@re,CHARINDEX(',',REVERSE(@re)+',')-1)[email protected] THEN ''                       ELSE CAST([email protected] as varchar)                   END                   +','+CAST(col2 as varchar)                END,           @pid=col2       FROM tb       WHERE [email protected]       ORDER BY col2       RETURN(STUFF(@re,1,2,'')           +CASE                WHEN RIGHT(@re,CHARINDEX(',',REVERSE(@re)+',')-1)[email protected] THEN ''               ELSE CAST([email protected] 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 [email protected]+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 [email protected]       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 [email protected]+1 THEN @re             ELSE @re                 +CASE                      WHEN RIGHT(@re,CHARINDEX(',',REVERSE(@re)+',')-1)[email protected] THEN ''                     ELSE CAST([email protected] as varchar)                 END                 +','+CAST(col2 as varchar)              END,         @pid=col2     FROM tb     WHERE [email protected]     ORDER BY col2     RETURN(STUFF(@re,1,2,'')         +CASE              WHEN RIGHT(@re,CHARINDEX(',',REVERSE(@re)+',')-1)[email protected] THEN ''             ELSE CAST([email protected] 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 [email protected]+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 [email protected]     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 --*/
  相关解决方案