SQL - 通过两个新列返回列中值的所有可能组合

Question

我想通过两个新列返回列中所有可能的值组合。例如。我的列由值 (A、B、C、D) 组成。这些值的可能组合为 (A,B)、(A,C)、(A,D)、(B,C)、(B,D)、(C,D)、(A,B,C) , (B,D,C), (D,C,A), (C,A,B) [备注:我不想考虑 (1) 只有一个值的组合，(2) 与所有值和 (3) 没有值的组合。因此我有 n 个不同值的 2^(n)-n-1-1 组合]。我想在两列中列出所有这些组合，如下所示。

考虑一下我从本专栏开始:

Col0
----
A
B
C
D

在 Col0 中，我想使用两列生成 10 种组合:

Col1 Col2
---- ----
1    A
1    B
2    A
2    C
3    A
3    D
4    B
4    C
5    B
5    D
6    C
6    C
7    A
7    B
7    C
8    B
8    C
8    D
9    C
9    D
9    A
10   D
10   A
10   B

如何在 SQL 中执行此操作？我使用 SQLite。

非常感谢!

Answer 1

我有一个解决方案，但需要进行两项更改...

1. 每个项目都必须有一个id(从1开始)
2. 输出 ID 可能不连续

 id | datum
----+-------
  1 |   A
  2 |   B
  3 |   C
  4 |   D

(我计算的输出id是每个排列的有效标识符，但我不会输出您不感兴趣的排列.. .)

 group_id | datum
----------+-------
    6     |   A
    6     |   B

    7     |   A
    7     |   C

    8     |   A
    8     |   D

    12    |   B
    12    |   C

    13    |   B
    13    |   D

    18    |   C
    18    |   D

    32    |   A
    32    |   B
    32    |   C

    33    |   A
    33    |   B
    33    |   D

    38    |   A
    38    |   C
    38    |   D

    63    |   B
    63    |   C
    63    |   D

http://dbfiddle.uk/?rdbms=sqlite_3.8&fiddle=87d670ecaba8b735cb3f95fa66cea96b

http://dbfiddle.uk/?rdbms=sqlite_3.8&fiddle=26e4f59874009ef95367d85565563c3c

WITH
  cascade AS
(
  SELECT
    1          AS depth,
    NULL       AS parent_id,
    id,
    datum,
    id         AS datum_id
  FROM
    sample

  UNION ALL

  SELECT
    parent.depth + 1,
    parent.id,
    parent.id * (SELECT MAX(id)+1 FROM sample) + child.id - 1,
    child.datum,
    child.id
  FROM
    cascade  AS parent
  INNER JOIN
    sample   AS child
      ON child.id > parent.datum_id
),
  travelled AS
(
    SELECT
      depth       AS depth,
      parent_id   AS parent_id,
      id          AS group_id,
      datum       AS datum,
      datum_id    AS datum_id
    FROM
      cascade
    WHERE
       depth NOT IN (1, (SELECT COUNT(*) FROM sample))

    UNION ALL

    SELECT
      parent.depth,
      parent.parent_id,
      child.group_id,
      parent.datum,
      parent.datum_id
    FROM
      travelled   AS child
    INNER JOIN
      cascade     AS parent
        ON parent.id = child.parent_id
)
SELECT
  group_id,
  datum
FROM
  travelled
ORDER BY
  group_id,
  datum_id

第一个 CTE 遍历所有可用的组合(递归地)，创建有向图。在此阶段，我不会排除一项或所有项的组合，但会排除等效的排列。

每个节点还有一个为其计算的唯一标识符。这些id之间存在间隙，因为计算也适用于所有排列，即使它们并未全部包含在内。

获取该图中的任何节点并走到最终父节点(再次递归)将始终给出与从图中的不同节点开始不同的组合。

因此，第二个 CTE 执行所有这些遍历，不包括“仅一项”和“所有项”的组合。

最终的选择只是按顺序输出结果。

id 中的间隙可能是可以避免的，但在工作日结束时，数学对我来说太难了。