如何将多列数据合并为一列?【邻接表模型查询】 [英] How to merge data from multiple columns into one? [Adjacency List Model Query]

问题描述

我有这个查询

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;

这就是结果的样子

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

As you can see it returns four columns. I want to merge distinct data of all four into one column.

我查看了类似的问题，但没有一个在查询中包含 Left Joins.我知道使用 union 是可能的，但我很挣扎.

I looked at similar questions but none of those has Left Joins in their query. I know it can be possible using union but I'm struggling.

谢谢.

推荐答案

正如您所说，您可以在查询结果上使用 UNION.在其余的答案中，我将向您展示使用 CTE 的解决方案，这是许多系统中可用的标准 SQL 语法，但不幸的是，在 MySQL 中没有.但是，要将此查询转换为 MySQL，您可以查看以下答案:你如何使用WITH"?MySQL中的子句?

As you said, you could use a UNION over the result of your query. In the rest of the answer I will show you a solution using CTE, which is a standard SQL syntax available in many systems, but, unfortunately, not in MySQL. However, to convert this query for MySQL you can look at the following answer: 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;

这篇关于如何将多列数据合并为一列?【邻接表模型查询】的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！