之前接到一个SQL问题,很久没写SQL了,自己还是有点胆怯,但是由于题目十分有意思,所以就接下了需求。
题目是:连续的序列并且值相同的用区间来体现
测试数据
最终体现的结果,即连续的序列并且值相同的用区间来体现
考虑SQL的效率,这里我给出了解法,主要还是使用分析函数来确定断点,用max(),min()这个聚合函数来表现连续
with lead_lag as
(select 1 as idd , 'a' as value from dual
union
select 2 as idd , 'b' as value from dual
union
select 3 as idd , 'a' as value from dual
union
select 5 as idd , 'a' as value from dual
union
select 6 as idd ,