代码如下， 如果是10g以上的版本， 用listagg来替换如下的wmsys.wm_concat
SQL> with t as (
2select '100' a, '201312' b from dual
3union all
4select '100' a, '201401' b from dual
5union all
6select '100' a, '201402' b from dual
7union all
8select '100' a, '201405' b from dual
9union all
10select '200' a, '201404' b from dual
11union all
12select '200' a, '201405' b from dual
13union all
14select '200' a, '201406' b from dual
15)
16selecta,
17
max(b) b
18from
19(select a,
20
wmsys.wm_concat(decode(count(*), 1, max(b), min(b) || '-' || max(b))) over(partition by a order by rn) b
21from
22(select a,
23
b,
24
max(rn) over(partition by a order by to_date(b, 'YYYYMM')) rn
25from
26(select a,
27
b,
28
case
29
when months_between(to_date(b, 'YYYYMM'), lag(to_date(b, 'YYYYMM')) over(partition by a order by to_date(b, 'YYYYMM'))) = 1 then
30
0
31
else
32
row_number() over(partition by a order by to_date(b, 'YYYYMM'))
33
end rn
34from t))
35group by a, rn)
36group by a;
A B
--- --------------------------------------------------------------------------------
100 201312-201402,201405
200 201404-201406
SQL>
|
