SQL如何查询出非连续性的单号?

Question

是这样的,某物品的编码是规则 的,
由ABC+流水号(3位长度)但由于某单在建后删除了,所以会漏好多号,如何用SQL语句查出这些单号?数据结构如下:AutoID    Pcode
-------  ------------------
1        ABC0001
2        ABC0002
4        ABC0004
6        ABC0006
-------------------
请问,如何查询出ABC0003,和ABC0005 ?

Answer 1

--> Title  : MSSQL求連續ID內數量合計-常見解決方案--> Author : wufeng4552--> Date   : 2009-12-04if object_id('tb') is not null drop table tbgocreate table tb(ID varchar(10),Num decimal(10,2)) insert tbselect '001',200.00 union allselect '002',200.00 union allselect '003',300.00 union allselect '007',500.00 union allselect '008',800.00 union allselect '009',200.00 union allselect '012',100.00 union allselect '013',100.00 union allselect '014',200.00 union allselect '017',100.00 union allselect '018',400.00 union allselect '019',300.00 -->方法 1 臨時表if object_id('tempdb..#t1')is not null drop table #t1if object_id('tempdb..#t2')is not null drop table #t2go--取出起號select cnt=identity(int,1,1),* into #t1 from tb t where not exists(select 1 from tb where id=t.id-1)order by t.id--取出止號select cnt=identity(int,1,1),* into #t2 from tb t where not exists(select 1 from tb where id=t.id+1)order by t.idselect n.[start]+'-'+n.[end]起止號,sum(num)合計from tb m,(select a.ID [start],b.ID [end] from #t1 a,#t2 b where a.cnt=b.cnt) nwhere m.ID between n.[start] and n.[end]group by n.[start]+'-'+n.[end]/*起止號                  合計--------------------- ---------------------------------------001-003               700.00007-009               1500.00012-014               400.00017-019               800.00 (4 個資料列受到影響) */--方法 2select case when min(t.id)!=max(t.id) then min(t.id)+'-'+max(t.id)else min(t.id)end 起止號,        sum(num)合計from(select ID,       cnt=cast(ID as int)-(select count(*)from tb n where m.ID>n.ID),       numfrom tb m)t group by cnt/*起止號                  合計--------------------- ---------------------------------------001-003               700.00007-009               1500.00012-014               400.00017-019               800.00 (4 個資料列受到影響) */--方法3select case when min(t.id)!=max(t.id) then min(t.id)+'-'+max(t.id)else min(t.id)end 起止號,        sum(num)合計from(select id,cnt=id-row_number()over(order by getdate()),num from tb)t group by cnt/*起止號                  合計--------------------- ---------------------------------------001-003               700.00007-009               1500.00012-014               400.00017-019               800.00 (4 個資料列受到影響) */本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/wufeng4552/archive/2009/12/04/4938218.aspx

Answer 2

http://topic.csdn.net/u/20090713/11/0f4e30d9-2a93-4e4b-900e-ab2734803e3d.html?8463

Answer 3

IF OBJECT_ID('TB') IS NOT NULL DROP TABLE TB
GO
CREATE TABLE TB(AutoID INT,   Pcode VARCHAR(50))
INSERT INTO TB
SELECT 1,        'ABC0001' UNION ALL
SELECT 2,        'ABC0002' UNION ALL
SELECT 4,        'ABC0004' UNION ALL
SELECT 6,        'ABC0007' SELECT 
'ABC'+RIGHT('0000'+CONVERT(VARCHAR(50),T2.NUMBER),4)
FROM TB T1
RIGHT JOIN (
SELECT NUMBER FROM MASTER..SPT_VALUES WHERE TYPE='P'
) T2 ON CONVERT(INT,STUFF(T1.PCODE,1,3,'') )=T2.NUMBER
WHERE T2.NUMBER<=(SELECT MAX(CONVERT(INT,STUFF(PCODE,1,3,'') )) FROM TB)
AND T2.NUMBER>=(SELECT MIN(CONVERT(INT,STUFF(PCODE,1,3,'') )) FROM TB)
AND T1.AUTOID IS NULL
/*
ABC0003
ABC0005
ABC0006
*/

Answer 4

create table tb(AutoID int,   Pcode varchar(10))
insert into tb values(1 ,       'ABC0001') 
insert into tb values(2 ,       'ABC0002') 
insert into tb values(4 ,       'ABC0004') 
insert into tb values(6 ,       'ABC0006')
goselect pcode = left(m.pcode,3) + right('000'+cast(cast(right(m.pcode,4) as int) + 1 as varchar),4) from
(select * , px = (select count(1) from tb where AutoID < t.AutoID) + 1 from tb t) m,
(select * , px = (select count(1) from tb where AutoID < t.AutoID) + 1 from tb t) n
where m.px = n.px - 1 and m.autoid <> n.autoid - 1drop table tb /*
pcode          
-------------- 
ABC0003
ABC0005（所影响的行数为 2 行）
*/

Answer 5

/*====================================================*/
-- Author: Ken Wong
-- Create date: 2009-12-17 11:36:07
-- Description:
/*====================================================*/
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb]([AutoID] int,[Pcode] varchar(7))
insert [tb]
select 1,'ABC0001' union all
select 2,'ABC0002' union all
select 4,'ABC0004' union all
select 6,'ABC0006'select 'ABC'+ right('0000'+LTRIM(number),4) as AutoID
from master..spt_values
where type = 'P'
and number >=(select min(AutoID) from [tb])
and number <=(select max(AutoID) from [tb]) 
and number not in (select AutoID from [tb])------------------------------
ABC0003
ABC0005