您可以联合两个查询，然后使用row_number窗口函数 Package 以生成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

字符串
| 水果_水果_ID|水果_ID|产品名称|水果数量|
| --|--|--|--|
| 1 | 1 |苹果| 3 |
| 2 | 1 |香蕉| 2 |
| 3 | 2 |苹果| 3 |
| 4 | 2 |香蕉| 6 |
| 5 | 3 |苹果| 3 |
| 6 | 3 |香蕉| 1 |
fiddle

其中一个选项是将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  */

型