mysql - 在MySQL中选择分层数据

Question

我的数据库中有这个表(关注者)。

follower | following
---------|----------
1        |2
1        |4
1        |5
1        |6
1        |7
1        |8
2        |4
4        |6

这记录了我的用户的所有关注者和关注者。我有这个查询，但不起作用。

SELECT following 
FROM followers 
WHERE follower 
IN (SELECT following 
    FROM followers 
    WHERE follower = 2) 
OR following IN (SELECT follower 
                 FROM followers 
                 WHERE following = 2)

我想要实现的是用户“2”关注的用户列表，与关注用户“2”的用户列表合并。不包括已经被用户“2”关注的所有人。并按照与用户“2”的相关程度排列。

理想情况下，我的数据输出将是这样的(但不会自动按照该顺序):

suggested_users
---------------
6
1
5
7
8

我实际上不知道如何提出这个问题，但是我如何得到这些结果？

Answer 1

如果您可以选择转储 mysql，某些 sql 变体可让您使用递归查询获取树:

http://www.postgresql.org/docs/current/static/queries-with.html

如果没有，有多种解决方案；通常是嵌套集合或嵌套间隔:

http://en.wikipedia.org/wiki/Nested_set_model

http://en.wikipedia.org/wiki/Nested_intervals

(关于 SO 有很多与两者相关的问题。)

<小时/>

What is the statement with Postgres? I might switch, MySQL seems a little soft :P

未经测试，但类似于:

with recursive tree (root, node) as (
select following as root, follower as node
  from followers
union all
select tree.root, followers.follower
  from followers
  join tree on tree.node = followers.following
)
select * from tree where root = 2;

优化后的版本如下:

with recursive tree (root, node) as (
select following as root, follower as node
  from followers
 where following = 2
union all
select tree.root, followers.follower
  from followers
  join tree on tree.node = followers.following
)
select * from tree;