代码之家 › 专栏 › 技术社区 › Dan

基于电子邮件获取好友的sql查询

join sql mysql

Dan · 技术社区 · 7 年前

| 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的共同朋友是

另一个输入示例是:

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

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 |

如何得到这个?

5 回复 | 直到 7 年前

Salman Arshad 7 年前

问题的关键是建立一个用户列表,这些用户是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

Pham X. Bach 7 年前

SELECT DISTINCT u.email
FROM
(
    SELECT r.receiver_user_id AS id, u.email
    FROM user_relations r 
    INNER JOIN users u
    ON r.requestor_user_id = u.user_id 
    WHERE r.friend_status = 1 AND u.email = 'Kanchhi@example.com'
    UNION ALL
    SELECT r.requestor_user_id AS id, u.email
    FROM user_relations r 
    INNER JOIN users u
    ON r.receiver_user_id = u.user_id 
    WHERE r.friend_status = 1 AND u.email = 'Kanchhi@example.com'
) k
INNER JOIN
(
    SELECT r.receiver_user_id AS id, u.email
    FROM user_relations r 
    INNER JOIN users u
    ON r.requestor_user_id = u.user_id 
    WHERE r.friend_status = 1 AND u.email = 'john@example.com'
    UNION ALL
    SELECT r.requestor_user_id AS id, u.email
    FROM user_relations r 
    INNER JOIN users u
    ON r.receiver_user_id = u.user_id 
    WHERE r.friend_status = 1 AND u.email = 'john@example.com'
) j
ON k.id = j.id
INNER JOIN users u 
ON k.id = u.user_id 
AND u.email NOT IN ('Kanchhi@example.com','john@example.com');

Charly 7 年前

试试这个

SELECT usu.email from users usu where usu.user_id in (
select          r.receiver_user_id
from sov.user_relations r 
where r.requestor_user_id in (
    SELECT  u.user_id from users u where u.email in ('Kanchhi@example.com','john@example.com') 
     )
and r.friend_status = 1      
group by r.receiver_user_id
having count(r.receiver_user_id)  > 1
);

LukStorms 7 年前

您可以从用户开始,然后加入关系。

SELECT 
case when u.user_id = r.receiver_user_id then requestor.email else receiver.email end as friend_email
FROM users u
JOIN user_relations r 
  ON (r.requestor_user_id = u.user_id OR r.receiver_user_id = u.user_id)
 AND r.friend_status = 1 
LEFT JOIN users requestor ON requestor.user_id = r.requestor_user_id
LEFT JOIN users receiver ON receiver.user_id = r.receiver_user_id
WHERE u.email in ('Kanchhi@example.com','john@example.com') 
GROUP BY friend_email
HAVING COUNT(DISTINCT u.user_id) > 1

结果:

friend_email
----------------
andy@example.com 
jetli@example.com

Ritul Lakhtariya 7 年前

您可以创建如下代码所示的额外函数

BEGIN
DECLARE limitCount INT DEFAULT 0;
DECLARE counter INT DEFAULT 0;
DECLARE res INT DEFAULT 0;
DECLARE temp TEXT;
SET limitCount = 1 + LENGTH(inputList) - LENGTH(REPLACE(inputList, ',',''));
simple_loop:LOOP
SET counter = counter + 1;
SET temp = SUBSTRING_INDEX(SUBSTRING_INDEX(inputList,',',counter),',',-1);
SET res = FIND_IN_SET(temp,targetList);
IF res > 0 THEN LEAVE simple_loop; END IF;
IF counter = limitCount THEN LEAVE simple_loop; END IF;
END LOOP simple_loop;
RETURN res;
END

find_in_set_extra('Kanchhi@example.com, john@example.com','john@example.com') OR WHAT EVER YOUR INPUTS AND OUTPUTS.