我有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_SEND
和ID_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