提问人：MazedAndConfused 提问时间：11/4/2023 最后编辑：marc_sMazedAndConfused 更新时间：11/4/2023 访问量：41
Oracle SQL 插入多行并从单个外键拆分数据
Oracle SQL inserting multiple rows and splitting data from single foreign key
问：
尝试进行插入，从表中获取其数据，并在另一个表中将其拆分为两行（或更多行），同时保留父表中的外键。
对于此示例，假设父表是 并且 has 列：Fruit
fruit_id, num_fruit
我插入的表是带有列的：Fruit_Sub
Fruit_Sub_id, Fruit_id_FK, fruit_name, num_sub_fruit
要插入的每一排水果都知道，从那里总是有 3 个苹果，其余的都是香蕉。Fruit_subFruit.num_fruit
如果看起来像这样：Fruit
fruit_id num_fruit
----------------------
1 5
2 9
3 4
我想像这样插入数据：Fruit_sub
fruit_sub_id fruit_id_fk fruit_name num_sub_fruit
-----------------------------------------------------
1 1 apples 3
2 1 bananas 2
3 2 apples 3
4 2 bananas 6
5 3 apples 3
6 3 bananas 1
到目前为止，我所有的裂缝甚至都无法识别为 SQL。这个有点超出了我的范围。
答：
0赞
Isolated
11/4/2023
#1
您可以合并两个查询，然后使用 row_number 窗口函数进行包装以生成sub_id。
select
row_number() over (order by fruit_id, fruit_name) as fruit_sub_id,
fruit_id,
fruit_name,
num_sub_fruit
from (
select
fruit_id,
'apples' as fruit_name,
3 as num_sub_fruit
from fruit
union
select
fruit_id,
'bananas',
num_fruit - 3
from fruit
)z
order by 1,2,3
| FRUIT_SUB_ID | FRUIT_ID | FRUIT_NAME | NUM_SUB_FRUIT |
|---|---|---|---|
| 1 | 1 | 苹果 | 3 |
| 2 | 1 | 香蕉 | 2 |
| 3 | 2 | 苹果 | 3 |
| 4 | 2 | 香蕉 | 6 |
| 5 | 3 | 苹果 | 3 |
| 6 | 3 | 香蕉 | 1 |
0赞
d r
11/4/2023
#2
其中一个选项是将 Case 表达式与 UNPIVOT 一起使用
WITH -- S a m p l e D a t a :
tbl AS
( Select 1 "FRUIT_ID", 5 "NUM_FRUIT" From Dual Union All
Select 2 "FRUIT_ID", 9 "NUM_FRUIT" From Dual Union All
Select 3 "FRUIT_ID", 4 "NUM_FRUIT" From Dual
)
-- M a i n S Q L :
Select ROWNUM "FRUIT_SUB_ID", FRUIT_ID_FK, FRUIT_NAME,
Case When FRUIT_NAME = 'apples' Then NUM_APPLES Else NUM_BANANAS End "NUM_SUB_FRUIT"
From ( Select FRUIT_ID "FRUIT_ID_FK", 'apples' "APPLES", 3 "NUM_APPLES", 'bananas' "BANANAS", NUM_FRUIT - 3 "NUM_BANANAS"
From tbl
) UNPIVOT ( FRUIT_NAME
FOR FRUIT IN(APPLES as 'apples', BANANAS as 'bananas') )
/* R e s u l t :
FRUIT_SUB_ID FRUIT_ID_FK FRUIT_NAME NUM_SUB_FRUIT
------------ ----------- ---------- -------------
1 1 apples 3
2 1 bananas 2
3 2 apples 3
4 2 bananas 6
5 3 apples 3
6 3 bananas 1 */
如果可能有一行的 NUM_FRUIT小于 3 - 您也可以使用 Case 表达式进行处理：
WITH -- S a m p l e D a t a :
tbl AS
( Select 1 "FRUIT_ID", 5 "NUM_FRUIT" From Dual Union All
Select 2 "FRUIT_ID", 2 "NUM_FRUIT" From Dual
)
-- M a i n S Q L :
Select ROWNUM "FRUIT_SUB_ID", FRUIT_ID_FK, FRUIT_NAME,
Case When FRUIT_NAME = 'apples' Then NUM_APPLES Else NUM_BANANAS End "NUM_SUB_FRUIT"
From ( Select FRUIT_ID "FRUIT_ID_FK",
'apples' "APPLES",
Case When NUM_FRUIT >= 3 Then 3 Else NUM_FRUIT End "NUM_APPLES",
'bananas' "BANANAS",
Case When NUM_FRUIT >= 3 Then NUM_FRUIT - 3 Else 0 End "NUM_BANANAS"
From tbl
) UNPIVOT ( FRUIT_NAME
FOR FRUIT IN(APPLES as 'apples', BANANAS as 'bananas') )
/* R e s u l t :
FRUIT_SUB_ID FRUIT_ID_FK FRUIT_NAME NUM_SUB_FRUIT
------------ ----------- --------- -------------
1 1 apples 3
2 1 bananas 2
3 2 apples 2
4 2 bananas 0 */
评论