求一条SQL断号统计语句

[xj116]

序列号 发票号
1 000917646
2 000917649
3 000917650
4 000917651
5 000917659
6 000917660
7 000917661
8 000917662
9 000917663
10 000917664
11 000917665
12 000917666
13 000917667
14 000917668
15 000917669
要求找出发票号的断号位置。

[xj116]

declare @tb table(序列号 int,发票号 varchar(255))
insert @tb
SELECT 1, '200510412708' UNION ALL
SELECT 2, '200510412709' UNION ALL
SELECT 3, '200510412711' UNION ALL
SELECT 4, '200510412712' UNION ALL
SELECT 5, '200510412713' UNION ALL
SELECT 6, '200510412719' UNION ALL
SELECT 7, '200510412720'



select * from @tb as a
where not exists(select 序列号 from @tb where 序列号=a.序列号+1 and 发票号=a.发票号+1) and
发票号<>(select max(发票号) from @tb)

运行后出现：消息 248，级别 16，状态 1，第 17 行
转换 varchar 值 '200510412709' 时溢出了整数列。超过了其中最大的整数值。怎么回事!

[csdyyr]

declare @tb table(序列号 int,发票号 bigint) 

[xj116]

谢谢楼上各位的友情回答!!

[等不到来世]

select * from tb a
where not exists (select 1 from tb where 发票号=a.发票号+1 or 发票号=a.发票号-1)

[csdyyr]

declare @tb table(序列号 int,发票号 varchar(20))
insert @tb
SELECT 1,  '000917646' UNION ALL 
SELECT 2,  '000917649' UNION ALL 
SELECT 3,  '000917650' UNION ALL 
SELECT 4,  '000917651' UNION ALL 
SELECT 5,  '000917659' UNION ALL 
SELECT 6,  '000917660' UNION ALL 
SELECT 7,  '000917661' UNION ALL 
SELECT 8,  '000917662' UNION ALL 
SELECT 9,  '000917663' UNION ALL 
SELECT 10,  '000917664' UNION ALL 
SELECT 11,  '000917665' UNION ALL 
SELECT 12,  '000917666' UNION ALL 
SELECT 13,  '000917667' UNION ALL 
SELECT 14,  '000917668' UNION ALL 
SELECT 15,  '000917669'

select * from @tb as a 
where not exists(select 1 from @tb where 序列号=a.序列号+1 and 发票号=a.发票号+1) and
发票号<>(select max(发票号) from @tb)

/*
序列号         发票号                  
----------- -------------------- 
1           000917646
4           000917651
*/

[百年树人]

[Quote=引用 2 楼 csdyyr 的回复:]
select * from @tb as a
where not exists(select 1 from @tb where 序列号=a.序列号+1 and 发票号=a.发票号+1) and
发票号<>(select max(发票号) from @tb)
[/Quote]

[liangCK]

--> liangCK小梁 于2008-10-21
--> 生成测试数据: #T
IF OBJECT_ID('tempdb.dbo.#T') IS NOT NULL DROP TABLE #T
CREATE TABLE #T (序列号 INT,发票号 VARCHAR(9))
INSERT INTO #T
SELECT 1,'000917646' UNION ALL
SELECT 2,'000917649' UNION ALL
SELECT 3,'000917650' UNION ALL
SELECT 4,'000917651' UNION ALL
SELECT 5,'000917659' UNION ALL
SELECT 6,'000917660' UNION ALL
SELECT 7,'000917661' UNION ALL
SELECT 8,'000917662' UNION ALL
SELECT 9,'000917663' UNION ALL
SELECT 10,'000917664' UNION ALL
SELECT 11,'000917665' UNION ALL
SELECT 12,'000917666' UNION ALL
SELECT 13,'000917667' UNION ALL
SELECT 14,'000917668' UNION ALL
SELECT 15,'000917669'

--SQL查询如下:

SELECT *
FROM
(
    SELECT 起始缺号=RIGHT('00000000'+RTRIM(发票号+1),9),
           终止缺号=(SELECT RIGHT('00000000'+RTRIM(min(发票号)-1),9)
                     FROM #T AS a
                     WHERE 发票号>t.发票号
                         AND NOT EXISTS(
                            SELECT *
                            FROM #T
                            WHERE 发票号=a.发票号-1
                          )
                      )
    FROM #T AS t
    WHERE NOT EXISTS
         (
          SELECT *
          FROM #T
          WHERE 发票号=t.发票号+1
         )
) AS t
WHERE 终止缺号 IS NOT NULL;

/*
起始缺号               终止缺号
------------------ ------------------
000917647          000917648
000917652          000917658

(2 行受影响)

*/