mysql - 连接 3 个表，SUM() 来自其中 2 个表的值 (MYSQL)

Question

我对如何获取每个商品的购买数量和投票数感到困惑。

执行两个简单的 JOIN 来连接以下 3 个表，会将 purchase_count 乘以 Votes 表中的条目数:

SELECT item_id, item_name, sum(backing_status) purchase_count, sum(vote) vote_count 
FROM Items
LEFT JOIN Purchases ON Items.item_id=Purchases.item_id
LEFT JOIN Votes ON Items.item_id=Votes.item_id
where purchase_status='bought' 
group by Items.item_id 

Table Items
item_id item_name
1           item_1
2           item_2
3           item_3

Table Purchases
item_id purchase_status 
1           bought          
2           bought          
1           bought          

Table Votes
item_id    vote 
1           1         
2           1          
3           -1         
1           -1
1           -1 

所需的输出是:

item_id item_name  purchase_count vote_count
1           item_1      2          -1 //sum of -2 downvotes + 1 upvote
2           item_2      1           1
3           item_3      0          -1

我可能需要子查询，但无法完全弄清楚查询。或者有更好的办法吗？

Answer 1

您应该能够使用以下代码，该代码使用具有两个聚合的子查询来获取结果:

select i.item_id,
  i.item_name,
  coalesce(p.TotalBought, 0)TotalBought,
  coalesce(v.vote_count, 0) vote_count
from items i
left join
(
  select item_id, count(*) TotalBought
  from purchases
  where purchase_status = 'bought'
  group by item_id
) p
  on i.item_id = p.item_id
left join
(
  select item_id, sum(vote) vote_count
  from votes
  group by item_id
) v
  on i.item_id = v.item_id;

参见SQL Fiddle with Demo 。

您也可以在没有两个子查询的情况下编写此代码，但您需要将 purchase_status 的 WHERE 过滤器放在 JOIN 上:

select i.item_id,
  i.item_name,
  count(p.item_id) TotalBought,
  coalesce(v.vote_count, 0) vote_count
from items i
left join purchases p
  on i.item_id = p.item_id
left join
(
  select item_id, sum(vote) vote_count
  from votes
  group by item_id
) v
  on i.item_id = v.item_id
group by i.item_id, i.item_name;

参见SQL Fiddle with Demo