sql - 连接递归交叉连接

Question

我需要以递归交叉连接的方式连接名称。我不知道该怎么做，我已经尝试使用 WITH RECURSIVE 进行 CTE，但没有成功。

我有一个这样的表:

group_id | name
---------------
13       | A
13       | B
19       | C
19       | D
31       | E 
31       | F 
31       | G 

期望的输出:

combinations
------------
ACE
ACF
ACG
ADE
ADF
ADG
BCE
BCF
BCG
BDE
BDF
BDG

当然，如果我添加第 4 个(或更多)组，结果应该成倍增加。

Answer 1

native Postgresql 语法:

SqlFiddleDemo

WITH RECURSIVE cte1 AS
(
  SELECT *, DENSE_RANK() OVER (ORDER BY group_id) AS rn
  FROM mytable
),cte2 AS
(
  SELECT 
    CAST(name AS VARCHAR(4000)) AS name,
    rn
  FROM cte1
  WHERE rn = 1
  UNION ALL
  SELECT
    CAST(CONCAT(c2.name,c1.name) AS VARCHAR(4000)) AS name
    ,c1.rn
  FROM cte1 c1
  JOIN cte2 c2
    ON c1.rn = c2.rn + 1
)
SELECT name as combinations
FROM cte2
WHERE LENGTH(name) = (SELECT MAX(rn) FROM cte1)
ORDER BY name;

之前:

希望您不介意我使用 SQL Server 语法:

示例:

CREATE TABLE #mytable(
   ID       INTEGER  NOT NULL
  ,TYPE     VARCHAR(MAX) NOT NULL
);
INSERT INTO #mytable(ID,TYPE) VALUES (13,'A');
INSERT INTO #mytable(ID,TYPE) VALUES (13,'B');
INSERT INTO #mytable(ID,TYPE) VALUES (19,'C');
INSERT INTO #mytable(ID,TYPE) VALUES (19,'D');
INSERT INTO #mytable(ID,TYPE) VALUES (31,'E');
INSERT INTO #mytable(ID,TYPE) VALUES (31,'F');
INSERT INTO #mytable(ID,TYPE) VALUES (31,'G');

主要查询:

WITH cte1 AS
(
  SELECT *, rn = DENSE_RANK() OVER (ORDER BY ID)
  FROM #mytable
),cte2 AS
(
  SELECT 
    TYPE = CAST(TYPE AS VARCHAR(MAX)),
    rn
  FROM cte1
  WHERE rn = 1
  UNION ALL
  SELECT
    [Type]      = CAST(CONCAT(c2.TYPE,c1.TYPE) AS VARCHAR(MAX))
    ,c1.rn
  FROM cte1 c1
  JOIN cte2 c2
    ON c1.rn = c2.rn + 1
)
SELECT *
FROM cte2
WHERE LEN(Type) = (SELECT MAX(rn) FROM cte1)
ORDER BY Type;

LiveDemo

我假设“交叉连接”的顺序取决于 ID 的升序。

• cte1 生成 DENSE_RANK() 因为您的 ID 包含间隙
• cte2 递归部分 CONCAT
• 主要查询只是过滤出所需的长度并对字符串进行排序