with t
as
(
select *,
row_number() over(order by number) rn
from test2
)
select case when min(number)=max(number) then cast(min(number) as varchar)
else cast(min(number) as varchar)+'~'+cast(max(number) as varchar) end as number
from t
group by number-rn
as
(
select *,
row_number() over(order by number) rn
from test2
)
select case when min(number)=max(number) then cast(min(number) as varchar)
else cast(min(number) as varchar)+'~'+cast(max(number) as varchar) end as number
from t
group by number-rn
with test as
(select 1 as number union all
select 2 as id union all
select 3 as id union all
select 4 as id union all
select 5 as id union all
select 8 as id union all
select 9 as id union all
select 10 as id union all
select 15 as id union all
select 16 as id union all
select 17 as id union all
select 18 as id ),
t
as
(
select *,
row_number() over(order by number) rn
from test
)
select case when min(number)=max(number) then cast(min(number) as varchar)
else cast(min(number) as varchar)+'-'+cast(max(number) as varchar) end as number
from t
group by number-rn--结果
number
-------------------------------------------------------------
1-5
8-10
15-18(3 行受影响)
这个帖子里的 第 2 个例子,和你这个需求类似。
with test as
(select 1 as number union all
select 2 as id union all
select 3 as id union all
select 4 as id union all
select 5 as id union all
select 8 as id union all
select 9 as id union all
select 10 as id union all
select 15 as id union all
select 16 as id union all
select 17 as id union all
select 18 as id ),
t
as
(
select *,
row_number() over(order by number) rn
from test
)
select ','+number from (select case when min(number)=max(number) then cast(min(number) as varchar)
else cast(min(number) as varchar)+'-'+cast(max(number) as varchar) end as number
from t
group by number-rn) as t for XML path ('')--结果
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
,1-5,8-10,15-18(3 行受影响)
哥们你也看了我帖子 这个帖子 就是我上次的面试题目。准确的说,我给你回复了,那个链接,就是我的回复。很多哥们都给的这个答案。比较牛B 比我之前用的递归快了4倍子在 12年的 ITPUB 上,是个比较抢眼的例子。
哥们你也看了我帖子 这个帖子 就是我上次的面试题目。准确的说,我给你回复了,那个链接,就是我的回复。很多哥们都给的这个答案。比较牛B 比我之前用的递归快了4倍子在 12年的 ITPUB 上,是个比较抢眼的例子。
13年10月开始转行做数据库的 以前干的物流