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
即将前面区间表示的数据,拆分成非区间表示。
10 个解决方案
#1
有没有人会啊,在线等......
#2
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,c4
from t1
connect by level <= c2 - c1 + 1
order by c1
c1 c4
---------------------------
1 10001 100
2 10002 100
3 10003 100
4 10004 100
5 10005 100
6 10007 200
7 10008 200
8 10009 200
9 10010 200
10 10011 200
11 10012 200
#3
可以这样实现
副表创建脚本
select distinct rt.beginresid - 1 + level, rt.price
from resource_tab rt
connect by level < rt.count + 1
副表创建脚本
create table resource_tab(
BeginResId number,
EndResId number,
count number,
price number
);
insert into resource_tab values(10001,10005,5,100);
insert into resource_tab values(10007,10012,6,200);
#4
呵呵,又加了个排序
select distinct rt.beginresid - 1 + level, rt.price
from resource_tab rt
connect by level < rt.count + 1
order by 1
#5
还没注意第三个字段是差数额 也差不多 替换下就可以了
#6
如果是算差的话要+1 如果直接用第三个字段来计算 不用+1 长度已经在这了 再加会超标的
#7
一条sql 查不出来的,不过可以写个程序转换到另一个表里:
DECLARE
CURSOR c1 IS
SELECT * FROM resource_2;
cr c1%ROWTYPE;
BEGIN
OPEN c1;
LOOP
FETCH c1
INTO cr;
EXIT WHEN c1%NOTFOUND;
FOR num1 IN cr.beginresid .. cr.endresid LOOP
INSERT INTO resource_1 (id, price) VALUES (num1, cr.price);
COMMIT;
END LOOP;
END LOOP;
END;
其中
resource_2 和你的resource表一样,resource_1建的一个结果表:
create table resource_1(id number,price number);
DECLARE
CURSOR c1 IS
SELECT * FROM resource_2;
cr c1%ROWTYPE;
BEGIN
OPEN c1;
LOOP
FETCH c1
INTO cr;
EXIT WHEN c1%NOTFOUND;
FOR num1 IN cr.beginresid .. cr.endresid LOOP
INSERT INTO resource_1 (id, price) VALUES (num1, cr.price);
COMMIT;
END LOOP;
END LOOP;
END;
其中
resource_2 和你的resource表一样,resource_1建的一个结果表:
create table resource_1(id number,price number);
#8
select distinct c1+level-1 c1,c4
from t1
connect by level <= c2 - c1 + 1
order by c1
试了一下这种方法,确实可以,但就是效率比较差,有没有办法优化一下。
from t1
connect by level <= c2 - c1 + 1
order by c1
试了一下这种方法,确实可以,但就是效率比较差,有没有办法优化一下。
#9
确实 distinct费时间 可以考虑先构造数字 再关联表查询出来
#10
with cte as(
select '10001' BeginResId, '10005' EndResId, 5 count, 100 price from dual
union all
select '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
#1
有没有人会啊,在线等......
#2
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,c4
from t1
connect by level <= c2 - c1 + 1
order by c1
c1 c4
---------------------------
1 10001 100
2 10002 100
3 10003 100
4 10004 100
5 10005 100
6 10007 200
7 10008 200
8 10009 200
9 10010 200
10 10011 200
11 10012 200
#3
可以这样实现
副表创建脚本
select distinct rt.beginresid - 1 + level, rt.price
from resource_tab rt
connect by level < rt.count + 1
副表创建脚本
create table resource_tab(
BeginResId number,
EndResId number,
count number,
price number
);
insert into resource_tab values(10001,10005,5,100);
insert into resource_tab values(10007,10012,6,200);
#4
呵呵,又加了个排序
select distinct rt.beginresid - 1 + level, rt.price
from resource_tab rt
connect by level < rt.count + 1
order by 1
#5
还没注意第三个字段是差数额 也差不多 替换下就可以了
#6
如果是算差的话要+1 如果直接用第三个字段来计算 不用+1 长度已经在这了 再加会超标的
#7
一条sql 查不出来的,不过可以写个程序转换到另一个表里:
DECLARE
CURSOR c1 IS
SELECT * FROM resource_2;
cr c1%ROWTYPE;
BEGIN
OPEN c1;
LOOP
FETCH c1
INTO cr;
EXIT WHEN c1%NOTFOUND;
FOR num1 IN cr.beginresid .. cr.endresid LOOP
INSERT INTO resource_1 (id, price) VALUES (num1, cr.price);
COMMIT;
END LOOP;
END LOOP;
END;
其中
resource_2 和你的resource表一样,resource_1建的一个结果表:
create table resource_1(id number,price number);
DECLARE
CURSOR c1 IS
SELECT * FROM resource_2;
cr c1%ROWTYPE;
BEGIN
OPEN c1;
LOOP
FETCH c1
INTO cr;
EXIT WHEN c1%NOTFOUND;
FOR num1 IN cr.beginresid .. cr.endresid LOOP
INSERT INTO resource_1 (id, price) VALUES (num1, cr.price);
COMMIT;
END LOOP;
END LOOP;
END;
其中
resource_2 和你的resource表一样,resource_1建的一个结果表:
create table resource_1(id number,price number);
#8
select distinct c1+level-1 c1,c4
from t1
connect by level <= c2 - c1 + 1
order by c1
试了一下这种方法,确实可以,但就是效率比较差,有没有办法优化一下。
from t1
connect by level <= c2 - c1 + 1
order by c1
试了一下这种方法,确实可以,但就是效率比较差,有没有办法优化一下。
#9
确实 distinct费时间 可以考虑先构造数字 再关联表查询出来
#10
with cte as(
select '10001' BeginResId, '10005' EndResId, 5 count, 100 price from dual
union all
select '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