sql - 如何选择所需的记录？

Question

表格:

• 产品:[id、名称、brand_id、is_published]
• 品牌:[id、名称、is_published]
• 奖项:[ID、姓名]
• 产品奖 [product_id、award_id]

如何选择已发布品牌的列表以及已发布品牌产品的奖项数量。

我对所有部分都很满意，除了在计算奖项时发出“is_published”限制。

我希望这一点很清楚；谁能建议在哪里挖掘？

Answer 1

我猜这就是您正在寻找的查询:

SELECT Brand.name, COUNT(Awards.id) AS Awards
FROM Brand
LEFT JOIN Product ON product.brand_id = Brand.id AND Product.is_published = TRUE /* This may need to be changed based on the data type of is_published */
LEFT JOIN ProductAwards ON ProductAwards.product_id = Product.id
LEFT JOIN Awards ON Awards.id = ProductAwards.award_id
WHERE Brand.is_published = TRUE /* As with Product.is_published, this clause is dependent on the column's data type */

要获得更明智的答案，您可能需要发布每个表的 SHOW CREATE TABLE 结果，以及您正在寻找的结果类型的示例。希望这对您有所帮助!