在SQL / mysql中管理层次结构 [英] Managing hierarchies in SQL/mysql

问题描述

我有一个下表User_Info。它包含所有用户信息和每个用户所属公司的ID。

I have a below table "User_Info". It carries all user info and the ID of the company that each user belongs to.

User_Id | User_Name | Company_Id
--------+-----------+--------------
1002    | User1     | 113
1003    | User2     | 114
1004    | User3     | 111

我有另一张表公司，其中包含公司信息及其相关母公司。

I have another table "Company" which has Company Information and its associated Parent Company.

id  | Company_Name    | Parent_Company_Id
----+-----------------+------------------
110 | WALMART         | NULL
111 | WALMART TEXAS   | 110
112 | WALMART DALLAS  | 111
113 | WALMART HOUSTON | 111
114 | WALMART KATY    | 113

如何获得以下输出？ User_Info表中的Company_id应列出该Company_id下的所有分层公司。

How do I get the below output? The Company_id in the User_Info table should list all the hierarchical companies under that Company_id.

user_id  | company_id
---------+--------------
1002     | 113
1002     | 111
1002     | 110
1003     | 114
1003     | 111
1003     | 110
1004     | 111
1004     | 110

我尝试过：



左连接和内连接。但它没有工作

What I have tried:

left join and inner join. But it doesent work

推荐答案

MySQL不支持分层查询，如果你做了任何搜索，你会看到许多不同的解决方案来解决这个问题。



我认为这个问题和答案最符合您的情况：

递归 - 递归mysql选择？ - 堆栈溢出 [ ^ ]



这是另一个类似的问题：

sql - 如何创建MySQL分层递归查询 - Stack Overflow [ ^ ]



对不起无法提供具体的解决方案。

MySQL doesn't support hierarchical queries, and if you have done any searching you will see many different solutions for this problem.

I think this question, and the answers, best match your case:
recursion - Recursive mysql select? - Stack Overflow[^]

Here is another similar question:
sql - How to create a MySQL hierarchical recursive query - Stack Overflow[^]

Sorry for not being able to provide a specific solution.

这篇关于在SQL / mysql中管理层次结构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！