mysql - 需要一个 MySQL 查询来显示有 child 的 parent 以及没有 child 的 parent

Question

我有一个 MySQL 查询，目前从一个包含三列的表中获取所有类别及其子类别。表中的每一行都包含一个 catid、一个类别和一个 parentid(如果没有父级则为零)。

SELECT t1.category AS lev1, 
   t2.category as lev2, 
   t3.category as lev3, 
   t4.category as lev4
FROM categories AS t1
LEFT OUTER JOIN categories AS t2 ON t2.parentid = t1.catid
LEFT OUTER JOIN categories AS t3 ON t3.parentid = t2.catid
LEFT OUTER JOIN categories AS t4 ON t4.parentid = t3.catid
WHERE t1.parentid = 0  AND t1.cat_type = 'content'
ORDER BY lev1, lev2, lev3, lev4

它向我返回一组数据，如下所示:

sample cat 1 | sample cat 4 
sample cat 1 | sample cat 5 
sample cat 1 | sample cat 6 
sample cat 2 | sample cat 7 
sample cat 2 | sample cat 8 
sample cat 3 | sample cat 9  |  sample cat 11
sample cat 3 | sample cat 10 |  sample cat 12

问题是我需要显示主要的父类别，就好像它们没有子类别一样。所以返回的数据应该是这样的”

sample cat 1 |               |
sample cat 1 | sample cat 5  |
sample cat 1 | sample cat 6  |
sample cat 2 |               |
sample cat 2 | sample cat 7  |
sample cat 2 | sample cat 8  |
sample cat 3 |               |
sample cat 3 | sample cat 9  |
sample cat 3 | sample cat 9  |  sample cat 11
sample cat 3 | sample cat 10 |
sample cat 3 | sample cat 10 |  sample cat 12

我修改了查询以自己获取顶级 Parent Cats，但显然这不是正确的方法。

    SELECT category AS lev1,    catid   AS lev1catid,
       '' as lev2,  '' as lev2catid,
       '' as lev3,  '' as lev3catid FROM categories WHERE parentid = 0  AND cat_type = 'content' UNION ALL

SELECT t1.category AS lev1,     t1.catid AS lev1catid, 
       t2.category as lev2,     t2.catid as lev2catid,
          t3.category as lev3,  t3.catid as lev3catid
    FROM cat
    egories AS t1
    LEFT OUTER JOIN categories AS t2 ON t2.parentid = t1.catid
    LEFT OUTER JOIN categories AS t3 ON t3.parentid = t2.catid
    WHERE t1.parentid = 0  AND t1.cat_type = 'content'
    ORDER BY lev1, lev2, lev3

如有任何帮助，我们将不胜感激。

Answer 1

一种方法是union all:

SELECT t1.category AS lev1, t2.category as lev2, t3.category as lev3, t4.category as lev4
FROM categories t1 JOIN
     categories t2
     ON t2.parentid = t1.catid JOIN
     categories t3
     ON t3.parentid = t2.catid JOIN
     categorie t4
     ON t4.parentid = t3.catid
WHERE t1.parentid = 0 AND t1.cat_type = 'content'
UNION ALL
SELECT t1.category AS lev1, t2.category as lev2, t3.category as lev3, NULL as lev4
FROM categories t1 JOIN
     categories t2
     ON t2.parentid = t1.catid JOIN
     categories t3
     ON t3.parentid = t2.catid
WHERE t1.parentid = 0 AND t1.cat_type = 'content'
UNION ALL
SELECT t1.category AS lev1, t2.category as lev2, NULL as lev3, NULL as lev4
FROM categories t1 JOIN
     categories t2
     ON t2.parentid = t1.catid 
WHERE t1.parentid = 0 AND t1.cat_type = 'content'
UNION ALL
SELECT t1.category AS lev1, NULL as lev2, NULL as lev3, NULL as lev4
FROM categories t1
WHERE t1.parentid = 0 AND t1.cat_type = 'content';