如何通过一条 SQL 查询获取产品及其购买次数？

Question

Buy 有一个外键 product_id。

因此，除了SELECT * FROM products;之外，我们还对获取每种产品的购买次数感兴趣。

Answer 1

类似这样的事情：

SELECT p.*, COUNT(buy.product_id) FROM product p INNER JOIN buy ON buy.product_id = p.id GROUP BY buy.product_id

应该可以解决问题。

Answer 2

连接

SELECT Products.*, COUNT(Buy.ID) AS Bought
FROM products
    LEFT OUTER JOIN Buy ON products.ID = Buy.product_id
GROUP BY Buy.product_id

子查询

SELECT 
    Products.*, 
    (SELECT COUNT(ID) FROM Buy WHERE product_id = Products.ID) AS Bought
FROM products

Answer 3

SELECT p.*,COUNT(b.product_id) FROM products p
INNER JOIN buy b ON p.id=b.product_id
GROUP BY b.product_id

这就是我能提供的关于您发布的信息的全部内容。如果上述方法不起作用，两者的表结构都会有很大帮助。