oracle连续出现3次数字,Oracle SQL-将连续数字设置为范围

在此问题中,您必须确定连续几周的顺序并将其分组.这是我的解决方案.

>使用LAG功能识别序列中的任何中断.

>使用SUM功能为每个序列分配一个组号.

>在每个组中找到开始和结束的一周.

>最后,使用LISTAGG函数汇总结果.

查询：

with x(wk, cost, startgroup) as(

--identify the start of a sequence

select wk, cost,

case when wk = lag(wk,1) over (partition by cost order by wk) + 1

then 0

else 1

end

from mytable

where id = '345'

),

y(wk, cost, grp) as(

--assign group number

select wk, cost,

sum(startgroup) over (partition by cost order by wk)

from x

),

z(wk, cost, grp) as(

--get the max/min week for each group

select case when min(wk) = max(wk)

then cast(min(wk) as varchar2(10))

else min(wk) ||'-'||max(wk)

end,

cost, grp

from y

group by cost, grp

)

--aggregate by cost

select listagg(wk,',') within group(order by grp),

cost

from z

group by cost;