表机构如下图:
gid snpch qsyzbm zzyzbm
156770020100519001343210343213
256770020100519001343215343299
356769520100519001343400343409
456769620100519001343410343499
556768920100519001343200343209
656768920100519001343214343214
756769720100519001343606343699
856769820100519001343600343605
输出结果要求是:
gid ybfm
1567689343200-343209,343214
2567695343400-343409
3567696343410-343499
4567697343606-343699
5567698343600-343605
6567700343210-343213,343215-343299
实现的sql语句如下:
SELECT IQ_NO,
REPLACE(SUBSTR(MAX(CASE
WHEN REPORT_NO IS NOT NULL OR LENGTH(REPORT_NO) = 0 THEN
SYS_CONNECT_BY_PATH(REPORT_NO, ';')
END),
2),
';',
',') REPORT_NO
FROM (SELECT IQ_NO,
REPORT_NO,
rn,
LEAD(rn) OVER(PARTITION BY IQ_NO ORDER BY rn) rn1
FROM (SELECT IQ_NO,
REPORT_NO,
ROW_NUMBER() OVER(ORDER BY REPORT_NO DESC) rn
FROM (select gid as IQ_NO,
case
when a.qsyzbm <> a.zzyzbm then
a.qsyzbm || '-' || a.zzyzbm
else
a.qsyzbm
end as REPORT_NO
from q_snff_bzgkyb a
where a.snpch = '20100519001' and gid ='567689'
order by qsyzbm)))
START WITH rn1 IS NULL
CONNECT BY rn1 = PRIOR rn
GROUP BY IQ_NO