--示例数据
DECLARE @piaohao TABLE (piaohao char(13))
INSERT @piaohao SELECT 'CKD2010100000'
UNION ALL SELECT 'CKD2010100002'
UNION ALL SELECT 'CKD2010100003'
UNION ALL SELECT 'CKD2010100004'
UNION ALL SELECT 'CKD2010100006'
UNION ALL SELECT 'CKD2010100007'
UNION ALL SELECT 'CKD2010100008'
UNION ALL SELECT 'CKD2010100009'
--求断号,不用循环语句.
FROM @piaohao
WHERE (CASE WHEN SUBSTRING(PIAOHAO,4,10) = (SELECT MAX(SUBSTRING(PIAOHAO,4,10)) FROM @piaohao)
THEN CONVERT(int,SUBSTRING(PIAOHAO,4,10))
ELSE CONVERT(int,SUBSTRING(PIAOHAO,4,10)) + 1
END
) NOT IN (SELECT CONVERT(int,SUBSTRING(PIAOHAO,4,10)) FROM @piaohao)但连续断号没取出来.
INSERT @piaohao SELECT 'CKD2010100000'
UNION ALL SELECT 'CKD2010100002'
UNION ALL SELECT 'CKD2010100003'
UNION ALL SELECT 'CKD2010100004'
UNION ALL SELECT 'CKD2010100006'
UNION ALL SELECT 'CKD2010100007'
UNION ALL SELECT 'CKD2010100008'
UNION ALL SELECT 'CKD2010100009'select 'CKD201010'+ right('000' + cast(m1+t.number as varchar),4) piaohao from
(
select cast(min(right(piaohao,4)) as int) m1, cast(max(right(piaohao,4)) as int) m2 from @piaohao
) m,master..spt_values t where t.type='p' and m1+t.number <= m2
and 'CKD201010'+ right('000' + cast(m1+t.number as varchar),4) not in (select piaohao from @piaohao)/*
piaohao
-----------------
CKD2010100001
CKD2010100005(所影响的行数为 2 行)
*/
INSERT @piaohao SELECT 'CKD2010100000'
UNION ALL SELECT 'CKD2010100002'
UNION ALL SELECT 'CKD2010100003'
UNION ALL SELECT 'CKD2010100004'
UNION ALL SELECT 'CKD2010100006'
UNION ALL SELECT 'CKD2010100007'
UNION ALL SELECT 'CKD2010100008'
UNION ALL SELECT 'CKD2010100009'
UNION ALL SELECT 'CKD2010100020'
UNION ALL SELECT 'CKD2010100021'
UNION ALL SELECT 'CKD2010100025'
select 'CKD201010'+ right('000' + cast(m1+t.number as varchar),4) piaohao from
(
select cast(min(right(piaohao,4)) as int) m1, cast(max(right(piaohao,4)) as int) m2 from @piaohao
) m,master..spt_values t where t.type='p' and m1+t.number <= m2
and 'CKD201010'+ right('000' + cast(m1+t.number as varchar),4) not in (select piaohao from @piaohao)/*
piaohao
-----------------
CKD2010100001
CKD2010100005
CKD2010100010
CKD2010100011
CKD2010100012
CKD2010100013
CKD2010100014
CKD2010100015
CKD2010100016
CKD2010100017
CKD2010100018
CKD2010100019
CKD2010100022
CKD2010100023
CKD2010100024(所影响的行数为 15 行)*/