-- 在ITPUB上看到的一个帖子,要求将以下数据
数据如下:
name dates num
张三 20120101 6
张三 20120102 6
张三 20120103 2
张三 20120104 2
张三 20120105 6
张三 20120106 6
张三 20120107 3
转换成以下
展示结果:
name from to value
张三 201201 201202 6
张三 201203 201204 2
张三 201205 201206 6
张三 201207 201207 3
-- 思路,运用LAG函数
-- 代码如下:
with t as
(
select '张三' name, '20120101' dates, 6 num from dual union all
select '张三' name, '20120102' dates, 6 num from dual union all
select '张三' name, '20120103' dates, 2 num from dual union all
select '张三' name, '20120104' dates, 2 num from dual union all
select '张三' name, '20120105' dates, 6 num from dual union all
select '张三' name, '20120106' dates, 6 num from dual union all
select '张三' name, '20120107' dates, 3 num from dual
)
select name,
min(dates) vfrom,
max(dates) vto,
num
from (
select name,
dates,
num,
max(flag) over(partition by name order by dates) rn
from (select t.*,
decode(num, lag(num) over(partition by name order by dates),0,
row_number() over(partition by name order by dates)) flag
from t))
group by name, rn, num;