如何将多列数据合并为一列?【邻接表模型查询】 [英] 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屋!