mysql - SQL 交叉匹配来自不同列和行的数据

Question

我一直很难弄清楚如何选择以下...

我有两张 table

Table_users                      Table_followers
| id | name     |                | follow_id | user_id |
| 1  | John     |                | 1         | 2       |
| 2  | Max      |                | 3         | 1       |
| 3  | Mary     |                | 2         | 1       |
| 4  | Robert   |                | 6         | 1       |
| 5  | Robin    |                | 1         | 5       |
| 6  | Sarah    |                | 1         | 6       |

1. 我想在单个查询中返回关注 John 的用户，而 John 正在关注他们，这样就称为 MATCH。
2. 然后是关注 John 的用户，FOLLOWERS
3. 最后是 John 关注的用户，关注

我使用了以下查询，但它返回了重复项，而且与我要查找的内容相去甚远

SELECT u.id, u.name, f.follower_id, f.user_id
FROM table_users u
LEFT JOIN table_followers f ON f.follower_id = u.id OR f.user_id = u.id
WHERE (f.user_id != 1 OR f.follower_id != 1) AND u.id != 1
ORDER BY u.id ASC";

想要的结果就像...

| uid | name     | match | follower | following |
| 2   | Max      | 1     | null     | null      |
| 6   | Sarah    | 1     | null     | null      |
| 3   | Mary     | null  | 1        | null      |
| 5   | Robin    | null  | null     | 1         |

用 SQL 有可能吗？

Answer 1

解决这个问题的一种方法是两次加入关注者表(一次用于关注者，一次用于关注)并执行如下查询:

select 
    u.id,
    u.name, 
    case when follow_id and user_id then 1 end as `match`,
    case when follow_id and user_id is null then 1 end as follower,
    case when user_id and follow_id is null then 1 end as following
from Table_users u
left join (select user_id from Table_followers where follow_id = 1) followers 
    on u.id = followers.user_id
left join (select follow_id from Table_followers where user_id = 1) following 
    on u.id = following.follow_id
where u.id <> 1 and (follow_id or user_id)
order by `match` desc, follower desc, following desc, u.id;

我确信有更有效和更清洁的方法来做到这一点，但已经晚了，旧大脑只以一半的速度工作；)

Sample SQL Fiddle

使用 MySQL，选择部分可以进一步简化为:

select 
    u.id,
    u.name, 
    ifnull((follow_id and user_id),0) as `match`,
    (follow_id and user_id is null) as follower,
    (user_id and follow_id is null) as following
from Table_users u

但是对于缺失值，这将为您提供 0 而不是 null。 (Sample)。