SQL：更新所有可能的组合
我有亲戚
+-----+----+
| seq | id |
+-----+----+
| 1 | A1 |
| 2 | B1 |
| 3 | C1 |
| 4 | D1 |
+-----+----+
并想在PostgreSQL中加入它
+----+-------+
| id | alter |
+----+-------+
| B1 | B2 |
| D1 | D2 |
+----+-------+
所以我得到了所有可能的替换组合（即替换或多或少的笛卡尔积）。所以组1没有更新，组2只有B2，组3只有D2，组4都有B2和D2。
结尾应该是这样的，但应该对更多人开放（就像D1的额外D3）
+-------+-----+----+
| group | seq | id |
+-------+-----+----+
| 1 | 1 | A1 |
| 1 | 2 | B1 |
| 1 | 3 | C1 |
| 1 | 4 | D1 |
| 2 | 1 | A1 |
| 2 | 2 | B2 |
| 2 | 3 | C1 |
| 2 | 4 | D1 |
| 3 | 1 | A1 |
| 3 | 2 | B1 |
| 3 | 3 | C1 |
| 3 | 4 | D2 |
| 4 | 1 | A1 |
| 4 | 2 | B2 |
| 4 | 3 | C1 |
| 4 | 4 | D2 |
+-------+-----+----+
编辑：
另一个可能的替换表可以是
+----+-------+
| id | alter |
+----+-------+
| B1 | B2 |
| D1 | D2 |
| D1 | D3 |
+----+-------+
可能会导致6组（我希望我没有忘记一个案例）
+-------+-----+----+
| group | seq | id |
+-------+-----+----+
| 1 | 1 | A1 |
| 1 | 2 | B1 |
| 1 | 3 | C1 |
| 1 | 4 | D1 |
| 2 | 1 | A1 |
| 2 | 2 | B2 |
| 2 | 3 | C1 |
| 2 | 4 | D1 |
| 3 | 1 | A1 |
| 3 | 2 | B2 |
| 3 | 3 | C1 |
| 3 | 4 | D2 |
| 4 | 1 | A1 |
| 4 | 2 | B2 |
| 4 | 3 | C1 |
| 4 | 4 | D3 |
| 5 | 1 | A1 |
| 5 | 2 | B1 |
| 5 | 3 | C1 |
| 5 | 4 | D2 |
| 6 | 1 | A1 |
| 6 | 2 | B1 |
| 6 | 3 | C1 |
| 6 | 4 | D3 |
+-------+-----+----+
如果你有三个替代品，比如
+----+-------+
| id | alter |
+----+-------+
| B1 | B2 |
| C1 | C2 |
| D1 | D3 |
+----+-------+
这将导致8组。到目前为止，我所尝试的并没有真正的帮助：
WITH a as (SELECT * FROM (values (1,'A1'),(2,'B1'), (3,'C1'), (4,'D1') ) as a1(seq, id) )
, b as (SELECT * FROM (values ('B1','B2'), ('D1','D2')) as b1(id,alter) )
---------
SELECT row_number() OVER (PARTITION BY a.id) as g, * FROM
a
CROSS JOIN b as b1
CROSS JOIN b as b2
LEFT JOIN b as b3 ON a.id=b3.id
ORDER by g,seq;
我很高兴有更好的标题建议。
共有3个答案
编辑到问题后更新答案
这个问题中棘手的部分是生成替换的powerset。然而，幸运的是postgres支持递归查询
让我们调用第一个表的源代码，第二个表的替换代码，我将避免使用另一个讨厌的名称alter：
CREATE TABLE source (seq, id) as (
VALUES (1, 'A1'), (2, 'B1'), (3, 'C1'), (4, 'D1')
);
CREATE TABLE replacements (id, sub) as (
VALUES ('B1', 'B2'), ('D1', 'D2')
);
需要生成要替换的ID的第一个功率集。空集可以省略，因为它无论如何都不适用于连接
在递归步骤中，连接条件rec.id
最后一步：
交叉连接扇出源N次，其中N是替换的非空组合的数量（有变化）
使用过滤后的runnign和在seq上生成组名。
子集未列出
WITH RECURSIVE rec AS (
SELECT ARRAY[(id, sub)] subset, id FROM replacements
UNION ALL
SELECT subset || (repl.id, sub), repl.id
FROM replacements repl
JOIN rec ON rec.id > repl.id
)
SELECT NULL subset, 0 set_name, seq, id FROM source
UNION ALL
SELECT subset
, SUM(seq) FILTER (WHERE seq = 1) OVER (ORDER BY subset, seq) set_name
, seq
, COALESCE(sub, source.id) id
FROM rec
CROSS JOIN source
LEFT JOIN LATERAL (
SELECT id, sub
FROM unnest(subset) x(id TEXT, sub TEXT)
) x ON source.id = x.id;
测验
使用替换值（'B1'，'B2'），（'D1'，'D2'），查询返回4组。
subset | set_name | seq | id
-----------------------+----------+-----+----
| 0 | 1 | A1
| 0 | 2 | B1
| 0 | 3 | C1
| 0 | 4 | D1
{"(B1,B2)"} | 1 | 1 | A1
{"(B1,B2)"} | 1 | 2 | B2
{"(B1,B2)"} | 1 | 3 | C1
{"(B1,B2)"} | 1 | 4 | D1
{"(D1,D2)"} | 2 | 1 | A1
{"(D1,D2)"} | 2 | 2 | B1
{"(D1,D2)"} | 2 | 3 | C1
{"(D1,D2)"} | 2 | 4 | D2
{"(D1,D2)","(B1,B2)"} | 3 | 1 | A1
{"(D1,D2)","(B1,B2)"} | 3 | 2 | B2
{"(D1,D2)","(B1,B2)"} | 3 | 3 | C1
{"(D1,D2)","(B1,B2)"} | 3 | 4 | D2
(16 rows)
使用替换值（'B1'，B2'），（'D1'，D2'），（'D1'，D3'），查询返回6个组：
subset | set_name | seq | id
-----------------------+----------+-----+----
| 0 | 1 | A1
| 0 | 2 | B1
| 0 | 3 | C1
| 0 | 4 | D1
{"(B1,B2)"} | 1 | 1 | A1
{"(B1,B2)"} | 1 | 2 | B2
{"(B1,B2)"} | 1 | 3 | C1
{"(B1,B2)"} | 1 | 4 | D1
{"(D1,D2)"} | 2 | 1 | A1
{"(D1,D2)"} | 2 | 2 | B1
{"(D1,D2)"} | 2 | 3 | C1
{"(D1,D2)"} | 2 | 4 | D2
{"(D1,D2)","(B1,B2)"} | 3 | 1 | A1
{"(D1,D2)","(B1,B2)"} | 3 | 2 | B2
{"(D1,D2)","(B1,B2)"} | 3 | 3 | C1
{"(D1,D2)","(B1,B2)"} | 3 | 4 | D2
{"(D1,D3)"} | 4 | 1 | A1
{"(D1,D3)"} | 4 | 2 | B1
{"(D1,D3)"} | 4 | 3 | C1
{"(D1,D3)"} | 4 | 4 | D3
{"(D1,D3)","(B1,B2)"} | 5 | 1 | A1
{"(D1,D3)","(B1,B2)"} | 5 | 2 | B2
{"(D1,D3)","(B1,B2)"} | 5 | 3 | C1
{"(D1,D3)","(B1,B2)"} | 5 | 4 | D3
(24 rows)
使用替换值（'B1'，B2'），（'C1'，C2'），（'D1'，D2'），查询返回8个组：
subset | set_name | seq | id
---------------------------------+----------+-----+----
| 0 | 1 | A1
| 0 | 2 | B1
| 0 | 3 | C1
| 0 | 4 | D1
{"(B1,B2)"} | 1 | 1 | A1
{"(B1,B2)"} | 1 | 2 | B2
{"(B1,B2)"} | 1 | 3 | C1
{"(B1,B2)"} | 1 | 4 | D1
{"(C1,C2)"} | 2 | 1 | A1
{"(C1,C2)"} | 2 | 2 | B1
{"(C1,C2)"} | 2 | 3 | C2
{"(C1,C2)"} | 2 | 4 | D1
{"(C1,C2)","(B1,B2)"} | 3 | 1 | A1
{"(C1,C2)","(B1,B2)"} | 3 | 2 | B2
{"(C1,C2)","(B1,B2)"} | 3 | 3 | C2
{"(C1,C2)","(B1,B2)"} | 3 | 4 | D1
{"(D1,D2)"} | 4 | 1 | A1
{"(D1,D2)"} | 4 | 2 | B1
{"(D1,D2)"} | 4 | 3 | C1
{"(D1,D2)"} | 4 | 4 | D2
{"(D1,D2)","(B1,B2)"} | 5 | 1 | A1
{"(D1,D2)","(B1,B2)"} | 5 | 2 | B2
{"(D1,D2)","(B1,B2)"} | 5 | 3 | C1
{"(D1,D2)","(B1,B2)"} | 5 | 4 | D2
{"(D1,D2)","(C1,C2)"} | 6 | 1 | A1
{"(D1,D2)","(C1,C2)"} | 6 | 2 | B1
{"(D1,D2)","(C1,C2)"} | 6 | 3 | C2
{"(D1,D2)","(C1,C2)"} | 6 | 4 | D2
{"(D1,D2)","(C1,C2)","(B1,B2)"} | 7 | 1 | A1
{"(D1,D2)","(C1,C2)","(B1,B2)"} | 7 | 2 | B2
{"(D1,D2)","(C1,C2)","(B1,B2)"} | 7 | 3 | C2
{"(D1,D2)","(C1,C2)","(B1,B2)"} | 7 | 4 | D2
(32 rows)
从您的评论中获得更多信息后，这似乎是您的案例：
收费站有一定数量的收费亭：
CREATE TABLE station (
station text PRIMARY KEY
, booths int NOT NULL -- number of cashiers in station
);
INSERT INTO station VALUES
('A', 1)
, ('B', 2)
, ('C', 1)
, ('D', 3);
对于给定的路线，假设--
CREATE OR REPLACE FUNCTION f_pathfinder(_route text[])
RETURNS TABLE (grp int, path text[]) LANGUAGE sql STABLE PARALLEL SAFE AS
$func$
WITH RECURSIVE rcte AS (
SELECT cardinality($1) AS hops, 1 AS hop, ARRAY[s.station || booth] AS path
FROM station s, generate_series(1, s.booths) booth
WHERE s.station = $1[1]
UNION ALL
SELECT r.hops, r.hop + 1, r.path || (s.station || booth)
FROM rcte r
JOIN station s ON s.station = _route[r.hop + 1], generate_series(1, s.booths) booth
WHERE r.hop < r.hops
)
SELECT row_number() OVER ()::int AS grp, path
FROM rcte r
WHERE r.hop = r.hops;
$func$;
简单通话：
SELECT * FROM f_pathfinder('{A,B,C,D}'::text[]);
结果：
grp | path
---: | :--------
1 | {1,1,1,1}
2 | {1,1,1,2}
3 | {1,1,1,3}
4 | {1,2,1,1}
5 | {1,2,1,2}
6 | {1,2,1,3}
或者使用未嵌套的数组（结果如您在问题中显示的那样）：
SELECT grp, seq, booth
FROM f_pathfinder('{A,B,C,D}'::text[])
, unnest(path) WITH ORDINALITY AS x(booth, seq); -- ①
结果：
grp | seq | booth --: | --: | :---- 1 | 1 | A1 1 | 2 | B1 1 | 3 | C1 1 | 4 | D1 2 | 1 | A1 2 | 2 | B1 2 | 3 | C1 2 | 4 | D2 3 | 1 | A1 3 | 2 | B1 3 | 3 | C1 3 | 4 | D3 4 | 1 | A1 4 | 2 | B2 4 | 3 | C1 4 | 4 | D1 5 | 1 | A1 5 | 2 | B2 5 | 3 | C1 5 | 4 | D2 6 | 1 | A1 6 | 2 | B2 6 | 3 | C1 6 | 4 | D3
分贝
变体的数量随着您路线中站点的数量而快速增长。它是M1*M2*..Mn，其中Mn是第n站的展位数。
①关于ORDINALITY：
- 带有元素号的PostgreSQL unnest（）
似乎您希望将替换表rpl中列出的一组更改中的所有可能组合应用于目标表tbl。
只有两行，形成4（2^n）个可能的组合很简单。对于一般解，我建议使用一个基本的组合函数来生成所有组合。有无数种方式。下面是一个纯SQL函数：
CREATE OR REPLACE FUNCTION f_allcombos(_len int)
RETURNS SETOF bool[] LANGUAGE sql IMMUTABLE PARALLEL SAFE AS
$func$
WITH RECURSIVE
tf(b) AS (VALUES (false), (true))
, rcte AS (
SELECT 1 AS lvl, ARRAY[b] AS arr
FROM tf
UNION ALL
SELECT r.lvl + 1, r.arr || tf.b
FROM rcte r, tf
WHERE lvl < _len
)
SELECT arr
FROM rcte
WHERE lvl = _len;
$func$;
类似于这里讨论的内容：
- 生成给定元素列表的所有组合，排序
仅2个替换行的示例：
SELECT * FROM f_allcombos(2);
{f,f}
{t,f}
{f,t}
{t,t}
WITH effective_rpl AS ( -- ①
SELECT *, count(alter) OVER (ORDER BY seq) AS idx -- ②
FROM tbl LEFT JOIN rpl USING (id)
)
SELECT c.grp, e.seq
, CASE WHEN alter IS NOT NULL AND c.arr[e.idx] THEN e.alter -- ③
ELSE e.id END AS id
FROM effective_rpl e
, f_allcombos((SELECT count(alter)::int FROM effective_rpl)) -- ④
WITH ORDINALITY AS c(arr, grp); -- ⑤
准确地产生您想要的结果。
分贝
① 某些替换可能在目标表中不匹配；因此，首先要确定有效的替代品。
② count（）仅统计非空值，因此这可以作为从f\u allcombos（）返回的基于1的数组的索引。
③仅在替换可用时替换，并且布尔数组对给定索引idx具有true。
④ 交叉连接将目标表中的行集与可能的替换组合数相乘
⑤ 我使用带有序数的生成“组号”。请参阅：
- 带有元素号的PostgreSQL unnest（）
我们可能会将其直接连接到函数中，但我更愿意将其保持为泛型。
旁白：“改变”在Postgres中是非保留的，但在标准SQL中是保留字。
所以组1没有更新，组2只有B2，组3只有D2，组4都是B2和D2。
由于该语句的逻辑不在表中，我决定将该逻辑添加到表c中，即向现有表a添加3个新列，这取决于必须考虑的字段选择。
WITH a as (SELECT * FROM (values (1,'A1'),(2,'B1'), (3,'C1'), (4,'D1') ) as a1(seq, id) )
, b as (SELECT * FROM (values ('B1','B2'), ('D1','D2')) as b1(id,alter) )
, c as (
SELECT a.seq, a.id,
COALESCE(b1.alter,a.id) as id2,
COALESCE(b2.alter,a.id) as id3,
COALESCE(b3.alter,a.id) as id4
FROM a
LEFT JOIN (SELECT * FROM b WHERE b.alter='B2') b1 ON a.id = b1.id
LEFT JOIN (SELECT * FROM b WHERE b.alter='D2') b2 ON a.id = b2.id
LEFT JOIN (SELECT * FROM b WHERE b.alter IN ('B2','D2')) b3 ON a.id = b3.id)
, d as (SELECT * FROM (values (1),(2), (3), (4) ) as d1(gr) )
SELECT d.gr,
CASE d.gr
WHEN 1 THEN c.id
WHEN 2 THEN c.id2
WHEN 3 THEN c.id3
WHEN 4 THEN c.id4 END as id
FROM d
CROSS JOIN c
ORDER by d.gr, c.seq
-
我已经研究过了 我想将Inspection\u Date和Sl\u No列的所有行更新为SFID组中的最新行。 生成的表应如下所示：- 我试过两种方法：- 帮助源：-来自此sqlite的帮助：使用第一行的值更新组中的所有行 代码1：- 错误：- 帮助来源：-MySQL-更新所有记录以匹配组中的最大值 代码2：- 错误：- 请帮助！！ 输入数据集示例 预期数据集示例
-
问题内容： 给定表： 名称对于所有人而言都是唯一的 哪种SQL查询可以生成所有可能的n！/（（（n-2）！2！）循环组合？ 假定Person的基数始终等于4 示例人物= {‘Anna’，’Jerome’，’Patrick’，’Michael’） 输出： 任何帮助，将不胜感激。谢谢！ 这是我的答案（我使用了oracle SQL）： 问题答案：
-
问题内容： 我有一个项目{a，b，c，d}的列表，当我需要生成所有可能的组合时， 您可以选择任意数量的项目 顺序不重要（ab = ba） 空集不被考虑 如果我们抓住可能性，那就应该是 我使用了以下递归方法： 当数组大时，有没有更有效的方法？ 问题答案： 将组合视为一个二进制序列，如果所有4个都存在，则得到1111，如果缺少第一个字母，则得到0111，依此类推。对于n个字母，我们将得到2 ^ n -
-
我有下表： 对于两组中的每一组，我想返回所有可能的值组合。对于组1，例如，可能的组合是（A， B）、（A， C）、（A， D）、（B， C）、（B， D）、（C， D）、（A， B， C）、（B， D， C）、（C， A， B）。类似地，对于组2，它是（A， B）、（A， C）、（B， C）[备注：我不想考虑（1）只有一个值的组合，（2）所有值的组合和（3）没有值的组合。因此，对于n个不同的值，我
-
问题内容： 如何从数据库“ x”的同一列中返回值的所有可能组合的列表？例如，我有： 我想返回所有可能组合的列表，例如， .... 问题答案： 您尚未说明正在使用哪个RDBMS，或者您是否要将组合限制为仅包含集合的2个元素。 这是使用分层查询的Oracle答案： SQL小提琴 Oracle 11g R2架构设置 ： 查询1 ： 结果 ： 查询2 ： 结果 ： 以及使用递归CTE的SQL Server