MySQL 从同一张表中获取链接结果

Question

我无法解决一个小的(希望是)MySQL 问题。我有一个名为链接的表。它包含一个 customer_id 字段和一个 linked_id 字段，基本上将客户帐户相互链接，其中 customer_id 处于领先地位。新创建的帐户可以自己生成帐户，我想查看由登录用户创建的所有帐户 + 子帐户创建的所有帐户。

表格如下所示:

+----+-------------+-----------+
| id | customer_id | linked_id |
+----+-------------+-----------+
|  1 |           1 |         5 |
|  2 |           1 |         2 |
|  3 |           1 |        11 |
|  4 |           1 |        13 |
|  5 |          13 |        14 |
|  6 |           3 |         4 |
|  7 |           7 |         8 |
+----+-------------+-----------+

因此，如果我以 customer_id 1 的用户身份登录，那么我想获取 linked_id 5,2,11,13(因为它们是直接连接)和 linked_id 14(因为该用户是由直接连接到 1 的用户)。

查询需要是子查询才能获取所有用户详细信息。我目前有:

SELECT username, firstname, lastname, email, active, level FROM customers WHERE id
IN (SELECT linked_id FROM links WHERE customer_id=1) or id=1;

这显然只直接返回直接连接和 id=1 的用户。

Answer 1

感谢 eggyal 让我走上正轨。看到相对的复​​杂性，我不会再为第一次无法破解它而感到羞愧了。

我最终做了一些研究，发现了一些在 mysql 中使用闭包表的不错的设置。我最终创建了一个存储过程来填充我的闭包表，当然还有新表 cust_closure。我通过链接表重命名为 cust_links。

客户链接:

+-------------+---------+------+-----+---------+----------------+
| Field       | Type    | Null | Key | Default | Extra          |
+-------------+---------+------+-----+---------+----------------+
| id          | int(11) | NO   | PRI | NULL    | auto_increment |
| customer_id | int(11) | YES  |     | NULL    |                |
| linked_id   | int(11) | YES  |     | NULL    |                |
+-------------+---------+------+-----+---------+----------------+

cust_closure:

+-------------+---------+------+-----+---------+-------+
| Field       | Type    | Null | Key | Default | Extra |
+-------------+---------+------+-----+---------+-------+
| customer_id | int(11) | YES  |     | NULL    |       |
| linked_id   | int(11) | YES  |     | NULL    |       |
| distance    | int(11) | YES  |     | NULL    |       |
+-------------+---------+------+-----+---------+-------+

然后添加存储过程:

CREATE PROCEDURE populate_cust_closure()
BEGIN
  DECLARE distance int;
  TRUNCATE TABLE cust_closure;
  SET distance = 0;
  -- seed closure with self-pairs (distance 0)
  INSERT INTO cust_closure (customer_id, linked_id, distance)
    SELECT customer_id, customer_id, distance
      FROM cust_links GROUP BY customer_id;

  -- for each pair (root, leaf) in the closure,
  -- add (root, leaf->child) from the base table
  REPEAT
    SET distance = distance + 1;
    INSERT INTO cust_closure (customer_id, linked_id, distance)
      SELECT cust_closure.customer_id, cust_links.linked_id, distance
        FROM cust_closure, cust_links
          WHERE cust_closure.linked_id = cust_links.customer_id
          AND cust_closure.distance = distance - 1;
  UNTIL ROW_COUNT()=0
  END REPEAT;
END // 

当我随后调用它生成的存储过程时:

mysql> select * from cust_closure;
+-------------+-----------+----------+
| customer_id | linked_id | distance |
+-------------+-----------+----------+
|           1 |         1 |        0 |
|           3 |         3 |        0 | 
|           7 |         7 |        0 | 
|          13 |        13 |        0 | 
|           1 |         5 |        0 |
|           1 |         2 |        0 |
|           1 |        11 |        0 |
|           1 |        13 |        0 |
|          13 |        14 |        0 |
|           1 |        14 |        1 |
|           3 |         4 |        0 |
|           7 |         8 |        0 |
+-------------+-----------+----------+

所以现在我原来的查询变成了:

SELECT username, firstname, lastname, email, active, level FROM customers WHERE id
IN (SELECT linked_id FROM cust_closure WHERE customer_id=1);

再次感谢 eggyal，希望这对以后的人有所帮助。