连接递归交叉连接。

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

当然，如果我添加第四个（或更多）组，结果应该会增加。

- frankhommers

Answer 1

本地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;

实时演示

我假设“cross join”的顺序取决于升序ID。

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

- Lukasz Szozda

---

太棒了！这就解决了问题。你对于顺序的假设在这个例子中是正确的；）实际上，我已经添加了一个显示序列列；） - frankhommers

Answer 2

在Postgres中，递归查询要简单一些：

WITH RECURSIVE t AS (  -- to produce gapless group numbers
   SELECT dense_rank() OVER (ORDER BY group_id) AS grp, name
   FROM   tbl
   )
, cte AS (
   SELECT grp, name
   FROM   t
   WHERE  grp = 1

   UNION ALL
   SELECT t.grp, c.name || t.name
   FROM   cte c
   JOIN   t ON t.grp = c.grp + 1
   )
SELECT name AS combi
FROM   cte
WHERE  grp = (SELECT max(grp) FROM t)
ORDER  BY 1;

基本逻辑与 @lad2025 提供的 SQL Server 版本相同，我添加了一些小的改进。

或者，如果你的最大组数不太大（实际上不能太大，因为结果集会呈指数级增长），你可以使用一个简单版本。最多支持 5 组：

WITH t AS (  -- to produce gapless group numbers
   SELECT dense_rank() OVER (ORDER BY group_id) AS grp, name AS n
   FROM   tbl
   )
SELECT concat(t1.n, t2.n, t3.n, t4.n, t5.n) AS combi
FROM        (SELECT n FROM t WHERE grp = 1) t1
LEFT   JOIN (SELECT n FROM t WHERE grp = 2) t2 ON true
LEFT   JOIN (SELECT n FROM t WHERE grp = 3) t3 ON true
LEFT   JOIN (SELECT n FROM t WHERE grp = 4) t4 ON true
LEFT   JOIN (SELECT n FROM t WHERE grp = 5) t5 ON true
ORDER  BY 1;

对于少数群体来说，这可能更快。使用LEFT JOIN .. ON true可以使其即使上层缺失也能工作。concat()会忽略NULL值。请使用EXPLAIN ANALYZE进行测试以确保。

SQL Fiddle显示了两者。

- Erwin Brandstetter

---

谢谢。我需要递归查询来实现我的目标。你的递归版本确实更简洁。我想我会在我的视图中使用它。 - frankhommers