MySQL聚合查询？

Question

我有以下mySQL代码：

SELECT 
      c.categoryId, 
      c.categoryName, c.categoryParent, 
      c.categoryDescription, 
      COUNT(p.productid) as totalProdsInCategory
    FROM categories as c
     LEFT JOIN normalproducts as p 
     ON c.categoryId = p.categoryid
WHERE c.categoryId = 41
    GROUP BY c.categoryId
    ORDER BY c.categoryParent ASC,  c.categoryName ASC

我希望能够包含另一个COUNT列。但这只能算作p.state = "active“的产品。这是我不正确的解决方案

SELECT 
      c.categoryId, 
      c.categoryName, c.categoryParent, 
      c.categoryDescription, 
      COUNT(p.productid) as totalProdsInCategory, 
      COUNT(q.productid) as totalActiveProdsInCategory
    FROM categories as c
     LEFT JOIN normalproducts as p 
     ON c.categoryId = p.categoryid
WHERE c.categoryId = 41
    GROUP BY c.categoryId
    ORDER BY c.categoryParent ASC,  c.categoryName ASC

有什么帮助吗？我不知道下一步该怎么走...

Answer 1

您可以使用SUM (CASE WHEN p.state = "active" THEN 1 ELSE 0 END)来获取您要查找的计数。

SELECT 
      c.categoryId, 
      c.categoryName, c.categoryParent, 
      c.categoryDescription, 
      COUNT(p.productid) as totalProdsInCategory, 
      SUM (CASE WHEN p.state = "active" THEN 1 ELSE 0 END) totalActiveProdsInCategory
    FROM categories as c
     LEFT JOIN normalproducts as p 
     ON c.categoryId = p.categoryid
WHERE c.categoryId = 41
    GROUP BY c.categoryId
    ORDER BY c.categoryParent ASC,  c.categoryName ASC

Answer 2

试试这个：

SELECT 
      c.categoryId, 
      c.categoryName, c.categoryParent, 
      c.categoryDescription, 
      COUNT(p.productid) as totalProdsInCategory, 
      SUM(IF(p.state='active',1,0)) as totalActiveProdsInCategory
    FROM categories as c
     LEFT JOIN normalproducts as p 
     ON c.categoryId = p.categoryid
WHERE c.categoryId = 41
    GROUP BY c.categoryId
    ORDER BY c.categoryParent ASC,  c.categoryName ASC

这与@Conrad的相似，但在我看来更简洁一些

Answer 3

这是可行的，但可能不太符合左连接方法：

SELECT        
c.catId,c.catName,
COUNT(p.productId) as totalProdsInCategory,
(SELECT count(*) from product as p where p.catId=1 AND p.state='active') as totalActive
FROM cat as c      
LEFT JOIN product as p ON c.catId = p.catId 
WHERE c.catId = 1     
GROUP BY c.catId     
ORDER BY c.catName ASC