尝试使用左连接和分组进行查询并在 MySQL 上左表的字段上出现问题

Question

我试图做一些查询，首先，我做了这个（并且有效）：

SELECT RP.id, RP.product_name, RP.price, RP.retailer_id, RP.product_id, 
count(G.id) AS duration, G.active, RP.retprod_id, P.pr_id AS video 
FROM retailer_products AS RP 
LEFT JOIN groups G ON RP.id=G.retailer_product_id 
INNER JOIN products P ON P.id=RP.product_id 
WHERE (RP.product_id IN (1)) 
GROUP BY RP.id;

但是当我做这个时，它给了我一个空集，不同之处在于它在查询的末尾有一个“HAVING”字段可能是：0、1或NULL（由于LEFT JOIN，我没有链接到表retailer_product的组）

SELECT RP.id, RP.product_name, RP.price, RP.retailer_id, RP.product_id, 
count(G.id) AS duration, G.active, RP.retprod_id, P.pr_id AS video 
FROM retailer_products AS RP 
LEFT JOIN groups G ON RP.id=G.retailer_product_id 
INNER JOIN products P ON P.id=RP.product_id 
WHERE (RP.product_id IN (1)) 
GROUP BY RP.id HAVING G.active=1;

因此，我尝试了以下方法，但没有人工作：

-- HAVING G.active=1

-- HAVING G.active=1 OR G.active=NULL

-- HAVING G.active0

在MySQL上处理此问题的正确方法是什么？提前致谢！

Answer 1

将值与 NULL 进行比较的正确方法是 IS NULL。因此，您需要添加HAVING G.active IS NULL OR G.active=1。您的代码 G.active=NULL 始终为 NULL (false)：

1 = NULL // NULL - false in conditions
NULL = NULL // NULL - false in conditions
NULL IS NULL // TRUE

Answer 2

为什么不在“Group”ON 条件上添加 HAVING 条件？

SELECT RP.id, RP.product_name, RP.price, RP.retailer_id, RP.product_id, 
count(G.id) AS duration, G.active, RP.retprod_id, P.pr_id AS video 
FROM retailer_products AS RP 
LEFT JOIN groups G ON RP.id=G.retailer_product_id AND G.active=1
INNER JOIN products P ON P.id=RP.product_id 
WHERE (RP.product_id IN (1)) 
GROUP BY RP.id;