我有2张桌子:

用户:

ID | NAME
1  | caio
2  | mike
3  | peter


发送:

ID | ID_SEND | ID_RECEIVE
1  |    1    |     2
2  |    2    |     3
3  |    3    |     2
4  |    2    |     1


每当用户向另一位用户发送卡片(通过电子邮件发送)时,该卡片就会被送入表格。

问题:我想写一个SELECT来知道用户在ID_SEND中的次数以及用户在ID_RECEIVE中的次数。我尝试过这样的事情:

SELECT
    user.email,
    COUNT(sends.id_receive) AS numberReceive,
    COUNT(sends.id_send) AS numberSend
FROM
    user
LEFT JOIN
    sends ON user.id = sends.id_send OR user.id = sends.id_receive
GROUP BY
    user.email


问题是ID_SENDID_RECEIVE都返回相同的值,这两个值之和……我要去哪里了?

最佳答案

LEFT JOIN两次,一次发送,一次接收

SELECT u.email,
       COUNT(r.id_receive) AS numberReceive,
       COUNT(s.id_send) AS numberSend
FROM user u
LEFT JOIN sends r ON u.id = r.id_receive
LEFT JOIN sends s ON u.id = s.id_send
GROUP BY u.email


或者,使用案例表达式对单个LEFT JOIN有条件计数:

SELECT u.email,
       SUM(case when u.id = s.id_receive then 1 else 0 end) AS numberReceive,
       SUM(case when u.id = s.id_send then 1 else 0 end) AS numberSend
FROM user u
LEFT JOIN sends s ON u.id IN (s.id_receive, s.id_send)
GROUP BY u.email

10-07 17:49