我有源 Tbl 喜歡
CID No_Of_Seats_Booked Seat_Numbers
-------------------------------------
1 3 01A01B01C
Tgt 表 O/P
CID Seat_id
------------
1 01A
1 01B
1 01C
uj5u.com熱心網友回復：
這是一種選擇：
SQL> with test (cid, no_of_seats_booked, seat_numbers) as
2 -- sample data
3 (select 1, 3, '01A01B01C' from dual union all
4 select 2, 2, '02A02B' from dual)
5 -- query you need begins here
6 select cid,
7 substr(seat_numbers, 1 (column_value - 1) * 3, 3) seat_id
8 from test cross join
9 table(cast(multiset(select level from dual
10 connect by level <= no_of_seats_booked
11 ) as sys.odcinumberlist))
12 order by cid, seat_id;
CID SEA
---------- ---
1 01A
1 01B
1 01C
2 02A
2 02B
SQL>
uj5u.com熱心網友回復：
另一種方法，只是為了多樣性：
with demo(cid, no_of_seats_booked, seat_numbers) as
( select 1, 3, '01A01B01C' from dual union all
select 2, 2, '02A02B' from dual
)
select d.cid
, regexp_substr(d.seat_numbers, '...', 1, r.rnum) seat_id
from demo d
cross apply (select rownum as rnum from dual connect by rownum <= d.no_of_seats_booked) r
order by d.cid, seat_id;
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/392589.html
上一篇：電影分級觸發器
下一篇：甲骨文下個季度的第一天