有朋友知道這個怎么實作嗎
uj5u.com熱心網友回復：
with gpdata as
(select 1 xuhao, '股A' gp, to_date('20200301', 'yyyyMMdd') rq
from dual
union all
select 2 xuhao, '股A' gp, to_date('20200302', 'yyyyMMdd') rq
from dual
union all
select 3 xuhao, '股A' gp, to_date('20200303', 'yyyyMMdd') rq
from dual
union all
select 4 xuhao, '股A' gp, to_date('20200305', 'yyyyMMdd') rq
from dual
union all
select 5 xuhao, '股A' gp, to_date('20200306', 'yyyyMMdd') rq
from dual
union all
select 6 xuhao, '股A' gp, to_date('20200309', 'yyyyMMdd') rq
from dual
union all
select 7 xuhao, '股B' gp, to_date('20200302', 'yyyyMMdd') rq
from dual
union all
select 8 xuhao, '股B' gp, to_date('20200303', 'yyyyMMdd') rq
from dual
union all
select 9 xuhao, '股B' gp, to_date('20200304', 'yyyyMMdd') rq
from dual
union all
select 10 xuhao, '股B' gp, to_date('20200305', 'yyyyMMdd') rq
from dual
union all
select 11 xuhao, '股B' gp, to_date('20200308', 'yyyyMMdd') rq
from dual
union all
select 12 xuhao, '股B' gp, to_date('20200309', 'yyyyMMdd') rq
from dual
union all
select 13 xuhao, '股B' gp, to_date('20200311', 'yyyyMMdd') rq
from dual)
select gp 股票,
to_char(min(rq), 'yyyyMMdd') 開始日期,
to_char(max(rq), 'yyyyMMdd') 結束日期,
max(rq) - min(rq) + 1 持續天數
from gpdata t
start with not exists (select 1
from gpdata
where gp = t.gp
and rq = t.rq - 1)
connect by prior gp = gp
and prior rq = rq - 1
group by gp, connect_by_root rq
having count (1) > 0
order by 1, 2;
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/220973.html
標籤：開發