时间戳

首页 > 解决方案 > sql查询根据email获取好友

mysql - sql查询根据email获取好友

问题描述

我有看起来像这样的示例数据

| users                              |
| user_id | email                    |
|---------|--------------------------|
| 1       | test@example.com |
| 2       | Kanchhi@example.com      |
| 3       | modi@example.com         |
| 4       | andy@example.com         |
| 5       | maya@example.com         |
| 6       | jetli@example.com        |
| 7       | john@example.com         |

| user_relations                                                          |
| user_relation_id | requestor_user_id | receiver_user_id | friend_status |
|------------------|-------------------|------------------|---------------|
| 1                | 2                 | 4                | 1             |
| 2                | 2                 | 6                | 1             |
| 3                | 2                 | 7                | 1             |
| 4                | 5                 | 2                | NULL          |
| 5                | 5                 | 7                | NULL          |
| 6                | 7                 | 2                | NULL          |
| 7                | 7                 | 4                | 1             |
| 8                | 7                 | 5                | 1             |
| 9                | 7                 | 6                | 1             |
| 10               | 4                 | 2                | 1             |
| 11               | 4                 | 3                | 1             |
| 12               | 4                 | 5                | 1             |
| 13               | 4                 | 6                | 1             |
| 14               | 4                 | 7                | 1             |

如果输入是这两封电子邮件：

Kanchhi@example.com, john@example.com

然后我预期的预期输出是这样的（顺序无关紧要）：

andy@example.com
jetli@example.com

在上面的示例中，用户 id 2 的朋友是用户 id (4, 6, 7)，用户 id 7 的朋友是用户 id (2, 4, 5, 6)。所以用户 id 2 和 7 的共同朋友是4 和 6。我需要共同用户 ID 的电子邮件地址。

另一个示例输入是：

andy@example.com, john@example.com

然后预期的输出是这样的：

jetli@example.com
Kanchhi@example.com
maya@example.com`

在上面的例子中，用户 id 4 的朋友是 (2, 6, 5, 3, 2)，用户 id 7 的朋友是 (6, 5, 2, 4)。所以共同朋友的用户 id 将是2, 6, 5。我需要输出中这些用户 ID 的电子邮件地址。

查询 1- 我试过但得到错误的结果：

SELECT  u.email 
       FROM user_relations r 
        LEFT JOIN users u   ON r.requestor_user_id = u.user_id
        LEFT JOIN users z   ON r.receiver_user_id = z.user_id
       where u.email in ('Kanchhi@example.com','john@example.com') or 
       z.email in ('Kanchhi@example.com','john@example.com') 
       and r.friend_status = 1 
       group by u.email 
      having count(u.email ) > 1

结果-但不正确：

| email               |
|---------------------|
| andy@example.com    |
| john@example.com    |
| Kanchhi@example.com |

如何得到这个？

标签： mysqlsqljoin

解决方案

问题的关键在于建立一个用户列表，这些用户是 Kanchi 和 John 的朋友，或者他们是朋友。然后计算那些出现在列表中两次的用户：

-- SELECT email FROM users WHERE userid IN (
SELECT friendid
FROM (
    SELECT requestor_user_id AS userid, receiver_user_id AS friendid
    FROM user_relations
    WHERE friend_status = 1 AND requestor_user_id IN (
        SELECT user_id
        FROM users
        WHERE email IN ('Kanchhi@example.com','john@example.com')
    )

    UNION ALL

    SELECT receiver_user_id, requestor_user_id
    FROM user_relations
    WHERE friend_status = 1 AND receiver_user_id IN (
        SELECT user_id
        FROM users
        WHERE email IN ('Kanchhi@example.com','john@example.com')
    )
) AS X
GROUP BY friendid
HAVING COUNT(DISTINCT userid) = 2

将结果与用户匹配是微不足道的。对于您的样本输入，结果是：

4    Andy     andy@example.com     ashutosh    2019-01-11 13:34:05
6    jetli    jetli@example.com    ashutosh    2019-01-11 13:34:05

推荐阅读