mysql - 带计数和连接表的 SQL 查询

Question

我有以下三个表:

客户
编号(PK)
姓名

产品
编号(PK)
姓名
价格

交易
编号(PK)
customer_id(FK ref Customer.id)
product_id(FK ref Product.id)
创建日期

我正在运行以下查询，它为我提供了每个客户使用的产品数量:

SELECT p.id, p.name, Count(p.id), p.price
FROM transaction t, product p, customer c
WHERE p.id = t.product_id
      AND  c.id = t.customer_id
      AND c.id = 1
group by p.price, p.name, p.id
ORDER BY p.name ASC;

这行得通，除了它只给我客户使用过的产品及其各自的数量。我想要的是所有产品的列表，其中客户未使用的产品的计数为 0。我在尝试解决这个问题时遇到了麻烦，因为我使用交易表作为客户和产品之间的连接表，而我找到每个客户每个产品的数量的方式是通过记录计数(正如您在上面的 SQL 代码中看到的那样)。

我能做到:

SELECT *
FROM product
WHERE product.name NOT IN (SELECT p.name
                           FROM transaction t, product p, customer c
                           WHERE p.id = t.product_id
                                 AND  c.id = t.customer_id
                                 AND c.id = 1);

然后以某种方式以编程方式将 0 分配给我的结果集中的计数，但我最终会得到两个列表，一个包含产品名称、每种产品的数量以及客户购买的每种产品的价格已使用，另一个列表将包含所有产品名称、计数为 0 以及客户未使用的每个产品的价格。

这可能是一个解决方案，但它似乎远非实用，因为 id 正在处理两个列表并且必须以编程方式分配值而不是处理一个包含我需要的所有数据的列表，这会影响排序等.

我想知道这是否可能，或者是否有另一种方法来看待这个问题。

我希望这已经足够清楚了。如果对此有任何见解，我将不胜感激!

毛里西奥 B.

Answer 1

永远不要在FROM 子句中使用逗号。 始终使用正确、明确、标准的 JOIN 语法。

你的问题具体是你需要一个left join:

select p.id, p.name, Count(t.id), p.price
from product p left join
     transaction t
     on p.id = t.product_id and
        t.customer_id = 1
group by p.price, p.name, p.id
order by p.name asc;

注意:您不需要客户表。 id在事务表中。