MYSQL:JOIN使SUM加倍 [英] MYSQL: JOIN makes SUM double

问题描述

这应该真的很容易.相信我，我已经研究了几个小时.我的查询:

This should be really easy. Believe me, I've been researching this for hours. My query:

SELECT not_piece.pid, part.name AS 'Part Name', SUM(qty_left) AS 'In Stock'
FROM not_piece 
JOIN part ON not_piece.pid = part.pid 
GROUP BY part.name;

只有两个表，not_piece和part.

Only two tables, not_piece and part.

select qty_left 
from not_piece 
where pid='M-MP-007r8'; 

返回5.

由于part.name在部件表中出现两次(没关系)，所以总和为10，而不是5.

Since the part.name appears twice in the parts table (that's fine), the sum is 10, not 5.

如何在不加总的情况下加入此联接?

How do I make this join without doubling the sum?

谢谢.

推荐答案

SELECT t.pid, p.name AS 'Part Name', t.InStock AS 'In Stock'
    FROM (SELECT pid, SUM(qty_left) AS InStock
              FROM not_piece
              GROUP BY pid) t
        INNER JOIN part p
            ON t.pid = p.pid

这篇关于MYSQL:JOIN使SUM加倍的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！