MySQL 从同一个连接表中的多个列中获取 COUNT

Question

我有 2 个表:

• 用户
• 消息(同时包含 id_user 和 id_user_from)

我需要一个用户基本信息列表，以及3个特殊栏目:

• 用户收到了 COUNT 条消息
• 用户发送的消息数量
• 用户消息的所有浏览量(发送和接收)的总和

这是我当前的查询:

SELECT
SUM(case when message.id_user = user.id_user THEN 1 ELSE 0 END) AS q_received,
SUM(case when message.id_user_from = user.id_user THEN 1 ELSE 0 END) AS q_sent,
SUM(message.views) AS views,
user.id_user AS id_user, user.name AS name, user.lastname AS lastname, user.date_login AS date_login
FROM user
    LEFT JOIN message ON message.id_user = user.id_user
ORDER BY user.date_login DESC

总和都是错误的。这是带有所有必要表和一些记录的 sqlfiddle 作为示例: http://sqlfiddle.com/#!9/4fd838/2

谢谢!

Answer 1

我会这样做:

select 
coalesce(SUM(recv),0) AS q_received,
coalesce(SUM(sent),0) AS q_sent,
coalesce(SUM(views),0) as views,
user.id_user AS id_user, user.name AS name, user.lastname AS lastname, user.date_login AS date_login
FROM user
left join
 (  
 select id_user, count(*) as recv, 0  as sent,sum(views) as views from message group by id_user
 union all
 select id_user_from, 0, count(*) as sent,sum(views) as views from message group by id_user_from
 ) t
on user.id_user=t.id_user
group by user.id_user
order by user.id_user;

Result:
q_received  q_sent  views   id_user name    lastname            date_login
4           2       32      1               Rigoberto   Santos  2018-03-21T17:37:28Z
0           2        6      2               Coquita Perlini     2018-03-21T17:34:59Z
1           1        2      3               Miguel  Bianchi     2018-03-21T14:26:11Z
0           0        0      4               Ramiro  Alvarado    2018-03-21T14:26:06Z
1           1       24      5               Andrea  Lucas       2018-03-21T17:35:04Z