有表resource字段如下：
BeginResId、EndResId、count、price
假设有如下数据：
10001 10005 5 100
10007 10012 6 200
通过sql拆分成如下数据
10001 100
10002 100
......
10005 100
10007 200
10008 200
......
10012 200
即将前面区间表示的数据，拆分成非区间表示。
------解决方案--------------------
- SQL code
with t1 as( select 10001 c1,10005 c2,5 c3,100 c4 from dual union all select 10007 c1,10012 c2,6 c3,200 c4 from dual)select distinct c1+level-1 c1,c4from t1connect by level <= c2 - c1 + 1order by c1 c1 c4---------------------------1 10001 1002 10002 1003 10003 1004 10004 1005 10005 1006 10007 2007 10008 2008 10009 2009 10010 20010 10011 20011 10012 200
------解决方案--------------------
可以这样实现
- SQL code
select distinct rt.beginresid - 1 + level, rt.price from resource_tab rtconnect by level < rt.count + 1
------解决方案--------------------
- SQL code
with cte as(select '10001' BeginResId, '10005' EndResId, 5 count, 100 price from dualunion allselect '10007', '10012', 6, 200 from dual ) select beginresid+level-1 resid,price from cte connect by level<=count and prior price=price and prior dbms_random.value is not null;结果为：RESID PRICE ---------------------- ---------------------- 10001 100 10002 100 10003 100 10004 100 10005 100 10007 200 10008 200 10009 200 10010 200 10011 200 10012 200 11 rows selected