sql - 使用列值的排列

Question

我正在尝试下表

key val
A   10
B   20
C   30
D   40

实现如下结果。 label 列按字母顺序显示 key 的所有组合。 total 列显示为 key 组合添加的 val。

label total
A     10
AB    30
ABC   60
ABCD  100
AC    40
AD    50
B     20
BC    50
BCD   90
BD    60
C     30
CD    70
D     40

虽然设法让查询继续进行，但仍然不太相信它。寻找更好的方法来获得相同的结果集。提前致谢。

with f (rn, key, val) as
(
  select rownum, a.* from
  (
      select 'A' key, 10 val from dual 
      union all select 'B', 20 from dual 
      union all select 'C', 30 from dual 
      union all select 'D', 40 from dual 
--      union all select 'E', 50 from dual
--      union all select 'F', 60 from dual
      order by 1
  ) a
)
,
-- irn, ikey, ival: anchor rownum, key and val to remember the starting row
-- rn, key, val: for the current row in the recursion
-- r1: current label in the recursion
-- r2: combination of anchor key and the current row key in the recursion
-- total: addition of all values for keys in r1
rs(irn, ikey, ival, rn, key, val, r1, r2, total) as
(
  select rn, key, val, rn, key, val, key, null, val from f
  union all
  select rs.irn, rs.ikey, rs.ival, f.rn, f.key, f.val, rs.r1 || f.key, rs.ikey || f.key, rs.total+f.val 
  from rs join f on (f.rn = rs.rn+1)
)
,
-- to add the additional rows required for the r2 col
-- when either r2 is not empty and not the same as r1 in rs
frs(irn, ikey, ival, rn, key, val, r1, r2, total) as
(
  select * from rs
  union all
  select irn, ikey, ival, rn, key, val, r2, r2, ival+val 
  from frs 
  where r2 is not null and r1 != r2
)
select r1, total from frs
order by 1
;

Answer 1

这是一种方法 - 使用自 Oracle 11.2 起可用的递归子查询分解:

with
-- Begin test data
     test_data ( key, val ) as (
       select 'A', 10 from dual union all
       select 'B', 20 from dual union all
       select 'C', 30 from dual union all
       select 'D', 40 from dual
     ),
-- End of test data (not part of the solution). 
-- SQL query begins with the keyword "with" from above and continues below this line.
     rec_cte ( label, total, last_symbol ) as (
       select key, val, key                                        -- anchor member
         from test_data
       union all
       select r.label || t.key, r.total + t.val, t.key             -- recursive member
         from rec_cte r join test_data t on r.last_symbol < t.key
     )
select   label, total 
from     rec_cte
order by label                         --  if needed
;

输出:

LABEL TOTAL
----- -----
A        10
AB       30
ABC      60
ABCD    100
ABD      70
AC       40
ACD      80
AD       50
B        20
BC       50
BCD      90
BD       60
C        30
CD       70
D        40

15 rows selected.