使用单个查询（递归查询）在mysql表中查找所有父级?mysql
我有这个架构
样本数据
| ID | TITLE | CONTROLLER | METHOD | PARENT_ID |
|---|---|---|---|---|
| 1 | Dashboard | admin | dashboard | 0 |
| 2 | Content | admin | content | 0 |
| 3 | Modules | admin | modules | 0 |
| 4 | Users | admin | users | 0 |
| 5 | Settings | admin | settings | 0 |
| 6 | Reports | admin | reports | 0 |
| 7 | Help | admin | help | 0 |
| 8 | Pages | content | pages | 2 |
| 9 | Media | content | media | 2 |
| 10 | Articles | content | articles | 2 |
| 11 | Menues | content | menues | 2 |
| 12 | Templates | content | templates | 2 |
| 13 | Themes | content | themes | 2 |
| 14 | Blog | content | blog | 2 |
| 15 | Forum | content | forum | 2 |
| 16 | Core Modules | modules | core_module | 3 |
| 17 | User Modules | modules | user_module | 3 |
| 18 | All Users | users | all_users | 4 |
| 19 | Groups | users | groups | 4 |
| 20 | Permissions | users | permissions | 4 |
| 21 | Import and Export | users | import_export | 4 |
| 22 | Send Email | users | send_mail | 4 |
| 23 | Login Records | users | login_records | 4 |
| 24 | General Settings | settings | general_settings | 5 |
| 25 | Email Settings | settings | email_settings | 5 |
| 26 | Popular Content | reports | popular_content | 6 |
| 27 | Most Active Users | reports | most_active_users | 6 |
| 28 | Documentation | help | documentation | 7 |
| 29 | About | help | about | 7 |
| 30 | Products | products | product | 17 |
| 31 | Categories | categories | category | 17 |
SQL Fiddle演示。我已经插入了一些示例数据。
查兰芝
我需要找到唱片标题的所有父母Categories。如何仅通过一个查询就可以获取所有父母？ 我的意思是我需要这个结果：
期望的输出
id | title | controller | method | url | parent_id
3 | Modules | admin | modules | (NULL) | 0
17 | User Modules | modules | user_module | (NULL) | 3
31 | Categories | categories | category | (NULL) | 17
假设我想使用其所有父项来获取条目，并且要使用where条件id = 31，那么它应该获取上述记录。
写回答
-
SELECT T2.id, T2.title,T2.controller,T2.method,T2.url FROM ( SELECT @r AS _id, (SELECT @r := parent_id FROM menu WHERE id = _id) AS parent_id, @l := @l + 1 AS lvl FROM (SELECT @r := 31, @l := 0) vars, menu m WHERE @r <> 0) T1 JOIN menu T2 ON T1._id = T2.id ORDER BY T1.lvl DESC;来源：stack overflow
2020-05-16 22:01:21赞同 展开评论 打赏
版权声明：本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
