php - 在关注者和关注者关系表上获取共同好友

Question

如何获得共同好友的 SQL 查询？

我的SQL

但我找不到共同的 friend

id | user_id
--------------
1   |  2
2   |  3
3   |  4
4   |  1

Relation Table 

id  | follower_id | following_id
--------------------------------
1   | 1          | 2
2   | 2          | 1
3   | 1          | 4
4   | 4          | 3
5   | 4          | 1

以下查询

"SELECT `user_id` FROM " . T_USERS . " WHERE `user_id` IN (SELECT `following_id` FROM " . T_FOLLOWERS . " WHERE `follower_id` = {$user_id} AND `following_id` <> {$user_id} AND `active` = '1') AND `active` = '1' ";

关注者查询

" SELECT `user_id` FROM " . T_USERS . " WHERE `user_id` IN (SELECT `follower_id` FROM " . T_FOLLOWERS . " WHERE `follower_id` <> {$user_id} AND `following_id` = {$user_id} AND `active` = '1') AND `active` = '1'";

Answer 1

SELECT `user_id` FROM " . T_USERS . "
 WHERE `user_id` IN (SELECT CASE WHEN `following_id`={$user_id} THEN `follower_id` ELSE `following_id` END user
                       FROM " . T_FOLLOWERS . "
                      WHERE `active` = '1'
                        AND ((`follower_id` = {$user_id} AND `following_id` <> {$user_id})
                          OR (`follower_id` <> {$user_id} AND `following_id` = {$user_id}))
                      GROUP BY user
                     HAVING count(*)=2
                    )
   AND `active` = '1'