min(t.SerialNumber)+'-'+max(t.SerialNumber) 号段,
CAST(max(SUBSTRING(t.SerialNumber,5,LEN(t.SerialNumber))) AS INT)-
CAST(min(SUBSTRING(t.SerialNumber,5,LEN(t.SerialNumber))) AS INT)+1 连续数量
from(
select SerialNumber,cnt=SUBSTRING(SerialNumber,5,LEN(SerialNumber))-row_number()over(order by getdate())
from @CardList WHERE IsActive=0
)t group by cnt 一条sql够呛吧 截取号段的字符串转换为数字，然后用数字来排序，这样不行吗？ DECLARE @CardList TABLE (id intidentity(1,1),SerialNumber nvarchar(16), IsActive INT)
INSERT INTO @CardList(SerialNumber,IsActive)
VALUES('No.B0013206',1),('No.B0013207',0),('No.B0013208',0),('No.B0013209',0),('No.B0013210',0),('No.B0013211',0),('No.B0013212',1),('No.B0013213',0),('No.B0013214',0),('No.B0013215',0),('No.B0013216',0),('No.B0013217',0),('No.B0013218',0),('No.B0013219',0),('No.B0013220',0),('No.B0013221',0),('No.B0013222',0),('No.B0013223',1)
;
with tmp as (
select
ROW_NUMBER() over (
order by
Id asc
) as IdNew,
*
from
@CardList
where
IsActive = 0
)
select
min(SerialNumber)+ '-' + max(SerialNumber) as 号段,
count(id) as 连续数量
from
(
select
a.IdNew,
a.Id,
a.SerialNumber,
case when ISNULL(
replace(a.SerialNumber, 'No.B', ''),
0
)-1 = replace(b.SerialNumber, 'No.B', '')
or replace(b.SerialNumber, 'No.B', '') is null then '连续' else '不连续' end as '连续标志'
from
tmp a
left join tmp b on replace(a.SerialNumber, 'No.B', '') = replace(b.SerialNumber, 'No.B', '')+ 1
) sss
group by
(sss.Id - sss.IdNew) sql server通过,其他不知道 最佳答案功力深厚 可真棒啊这些答案 NULL2019 发表于 2022-8-3 15:34
DECLARE @CardList TABLE (id intidentity(1,1),SerialNumber nvarchar(16), IsActive INT)
INSERT INTO ...
目前在NO.是连续的情况下是可行，如果NO.中间不连续就有问题了 woliuguojian 发表于 2022-8-3 17:38
目前在NO.是连续的情况下是可行，如果NO.中间不连续就有问题了
基于楼主给的数据写的,只能是抛砖引玉,如果数据不是这样,要根据实际情况调整的
页:
[1]
2