SQL 查询不连续的号码,按号码段分段显示,并统计分段数量。

发布网友发布时间：2022-04-26 17:38

共2个回答

热心网友时间：2022-04-11 20:14

declare @AA table(years int , number int)
insert into @AA values (2014,45201521)
insert into @AA values (2014,45201522)
insert into @AA values (2014,45201523)
insert into @AA values (2014,45201524)
insert into @AA values (2014,45201525)
insert into @AA values (2014,45201526)
insert into @AA values (2014,45201527)
insert into @AA values (2014,45201528)
insert into @AA values (2014,45201532)
insert into @AA values (2014,45201535)
insert into @AA values (2014,45201536)
insert into @AA values (2013,45070121)
insert into @AA values (2013,45070122)
insert into @AA values (2011,45010022)
insert into @AA values (2011,45010023)
insert into @AA values (2012,48038811)
insert into @AA values (2012,48038815)

select 年份,case when start_num!=end_num then convert(varchar(20),start_num)+'-'+convert(varchar(20),end_num)   else convert(varchar(20),start_num) end as 票段号,份数 from (
select years 年份, min(number) as start_num, max(number) as end_num,COUNT(years) as 份数
  from
(select years, number, number - ROW_NUMBER() over(order by number) as grp
   from @AA) m
  group by grp,years )t

order by 年份,票段号 asc

热心网友时间：2022-04-11 21:32

sqlserver的写法

select a.year, MIN(a.number) as min_no, MAX(a.number) as max_no, COUNT(1) as cnt
from(
select year, number, (number - ROW_NUMBER() over(order by year asc, number asc)) as cnt
from AA
) a
group by a.year, a.cnt

参考自：http://dodomail.iteye.com/blog/838937

思路是：把数据都从小到大排好序，标上序号，用票号-序号，连续的数字差值相同