SQL查询从备用名称生成可能的组合

Question

我在一个表中有一列，它由分号分隔的产品名称组成，如' a;B;C'， 'X;Y'， 'B; a;Z;T'等。在另一个表中，我有product_name和alternate_name的映射。下面是alterate_data表

的数据

Product_name  Alternate_name
A              A
A              2
B              B
B              4
C              C
C              6
C              7

在上表中，product_name也可以与alternate_name相同。

我想使用替代名称生成所有可能的产品名称组合。'A;B;C'的预期输出如下

A;B;C
A;B;6
A;B;7
A;4;C
2;B;C
A;4;6
A;4;7
2;B;6
2;B;7
2;4;C
2;4;6
2;4;7

如何生成上述所有可能的组合?

Answer 1

给定特定的组大小(例如3)，您可以使用子集的笛卡尔积来完成此操作，例如:

with
a as (select alternate_name from alternate_data where product_name = 'A'),
b as (select alternate_name from alternate_data where product_name = 'B'),
d as (select alternate_name from alternate_data where product_name = 'C')
select
a.alternate_name,
b.alternate_name,
c.alternate_name
from
a full join b full join c
order by
1, 2, 3

但是你后来说组可以是任意长度，这种变化不是SQL很适合的

Answer 2

试试这个:

with alternatives(Product_name, Alternate_name) as (
select 'A', 'A' from dual union all
select 'A', '2' from dual union all
select 'B', 'B' from dual union all
select 'B', '4' from dual union all
select 'C', 'C' from dual union all
select 'C', '6' from dual union all
select 'C', '7' from dual -- union all
)
, products(id, names) as (
select 1, 'A;B;C' from dual union all
select 2, 'B;A' from dual
)
, expanded(lvl, pnames, name, alternate_name) as (
select lvl, names, name, alternate_name from (
select level as lvl, names, regexp_substr(names,'[^;]+',1,level) as name
from products
connect by level <= regexp_count(names,';')+1 and id = prior id and prior sys_guid() is not null
)
join alternatives on name = product_name
)
, rebuild(lvl, pnames, names) as (
select lvl, pnames, alternate_name
from expanded where lvl = 1
union all

select e.lvl, r.pnames, r.names || ';' || e.alternate_name
from rebuild r
join expanded e on e.lvl = r.lvl + 1
)
select names from rebuild
where lvl = regexp_count(pnames,';')+1
;

B;A
B;2
B;B
B;4
4;A
4;2
4;B
4;4
2;A;C
2;A;6
2;A;7
2;2;C
2;2;6
2;2;7
2;B;C
2;B;6
2;B;7
2;4;C
2;4;6
2;4;7
A;A;C
A;A;6
A;A;7
A;2;C
A;2;6
A;2;7
A;B;C
A;B;6
A;B;7
A;4;C
A;4;6
A;4;7