mysql - 需要一个 MySQL 查询来显示有孩子的父母以及没有孩子的父母
问题描述
我有一个 MySQL 查询,它目前从一个三列表中获取所有类别及其子类别。表中的每一行都包含一个 catid、一个类别和一个 parentid(如果没有 parent,则为零)。
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
任何帮助将不胜感激。
解决方案
一种方法是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';
推荐阅读
- c - 如何在数组中存储 1 000 000 000 个项目?
- amazon-web-services - 如何从 VPN 访问 AWS ElastiCache (Redis)
- ios - 如何在先前转换的 UIView 上正确地为转换设置动画
- database - 如何删除最后少于500的大文本文件中的行
- dart - 是否可以使 Flutter 中的字符具有相同的宽度?
- python - 熊猫:如何计算日期之前所有出现的id
- ios - UIButtons 没有出现在 UIScrollView 中
- kubernetes - 可以同时拥有 l4 和 l7 ingress-nginx 吗?
- c++ - 有什么方法可以使虚拟 C++ 方法在视觉上与 Visual Studio 中的非虚拟方法不同?
- javascript - 如何从 Cloud Functions HTML 文件访问本地 JavaScript 文件?