mysql - 如何将多列数据合并为一列？ 【邻接表模型查询】

Question

我有这个问题

SELECT t1.categoryid AS lev1, t2.categoryid as lev2, t3.categoryid as lev3, t4.categoryid as lev4
FROM category AS t1
LEFT JOIN category AS t2 ON t2.parent = t1.categoryid
LEFT JOIN category AS t3 ON t3.parent = t2.categoryid
LEFT JOIN category AS t4 ON t4.parent = t3.categoryid
WHERE t1.categoryid = 4149418031;

结果是这样的

如您所见，它返回四列。我想将所有四个的不同数据合并到一列中。

我看过类似的问题，但没有一个在他们的查询中有左连接。 我知道使用 union 是可能的，但我正在努力。

谢谢。

Answer 1

如您所说，您可以对查询结果使用 UNION。在答案的其余部分，我将向您展示一个使用 CTE 的解决方案，这是一种在许多系统中可用的标准 SQL 语法，但不幸的是，在 MySQL 中不可用。但是，要将此查询转换为 MySQL，您可以查看以下答案:How do you use the "WITH" clause in MySQL?

WITH query AS (
  SELECT t1.categoryid AS lev1, t2.categoryid as lev2, t3.categoryid as lev3, t4.categoryid as lev4
  FROM category AS t1
  LEFT JOIN category AS t2 ON t2.parent = t1.categoryid
  LEFT JOIN category AS t3 ON t3.parent = t2.categoryid
  LEFT JOIN category AS t4 ON t4.parent = t3.categoryid
  WHERE t1.categoryid = 4149418031)
SELECT lev1 AS category_value FROM query
  UNION
SELECT lev2 AS category_value FROM query
  UNION
SELECT lev3 AS category_value FROM query
  UNION
SELECT lev4 AS category_value FROM query;