mysql - 管理 MySQL 层次结构数据

Question

我有一个下面的“User_Info”。它携带了所有用户信息以及每个用户所属公司的ID。

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

我有另一个表“公司”，其中包含公司信息及其关联的母公司。

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 下的所有分层公司。

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

Answer 1

您可以使用此查询(免责声明如下):

select user_id,
       company_id
from   (
        select     u.id as user_id,
                   c.id as company_id,
                   c.parent_company_id,
                   u.company_id as user_company_id
        from       user_info u,
                   company c
        order by   1, 2 desc
        limit      10000000000000000000
       ) base
where  company_id in (user_company_id, @p)
and    if(@p := parent_company_id, 1, 1)

免责声明

MySql 并不真正适合进行分层查询，因为它不支持 ISO 标准公用表表达式，也不支持递归语法。

但是MySql确实支持使用可以在SQL语句执行期间修改的变量。这通常不是获取结果的可靠方法，因为 MySql 在执行查询时可以自由更改计算顺序和中间结果的顺序。

但是如果您愿意冒这个风险，上面的查询会将 MySql 引导到某个序列，尽管这是有代价的:查询在两个表之间执行笛卡尔积。

条件

有一个条件:公司记录的父 ID 值都不能大于自己的 ID。所以parent_company_id < id OR parent_company_id IS NULL所有记录都必须为真。否则上述查询将不会给出完整的结果。

查询说明

首先，内部查询返回笛卡尔积。顺序很重要。这里假设 MySql 实际上会应用该顺序，因为理论上，MySql 可以忽略这一点，并且可能使用一个有趣的索引。增加 MySql 应用 order by 可能性的一种方法就是设定一个限度。对于低限制，很明显 MySql 必须应用该顺序来识别正确的记录。非常高的限制的目标是强制 MySql 应用此顺序，但仍获取所有记录。

主要逻辑在where条款。最初是@p变量是 null ，因此当公司与用户的公司匹配时，第一个条件实际上会成立。当这种情况发生时， where 的第二个“条件”条款也将被评估。这确实是一个假条件，因为它总是返回 1，因此始终为真。但在评估时，变量 @p更新为母公司 ID，这是该“条件”的唯一目的。

所以现在测试另一条记录时，公司ID可以是用户的公司，也可以是当前存储在@p中的母公司ID 。很明显，记录的顺序必须先按用户 ID，然后按公司 ID，其中公司父项排在其子项之后。这就是内部查询按降序排列公司 ID 的原因。

这是SQL fiddle .