使用MySQL连接两个表以进行聚合？

Question

我需要连接表来进行聚合。我在这方面很烂。这是我的场景：

CATEGORIES
CatID | CategoryName | Parent
1     | Cars         | NULL
2     | Electronics  | NULL
3     | DVD          | 2
4     | Blu_ray      | 2
5     | Shoes        | NULL

所以基本上，最顶层的元素没有父元素。然后我有

PRODUCTS
ProdID | Prod Name        | CatID
1      | DVD Player 1     | 3
2      | Blu-Ray Player   | 3
3      | Nike             | 5
4      | DVD Player 2     | 3

我想以..。

CATEGORIES
CatID | CategoryName | Parent | totalProds
1     | Cars         | NULL   | 0
2     | Electronics  | NULL   | 0
3     | DVD          | 2      | 2
4     | Blu_ray      | 2      | 1
5     | Shoes        | NULL   | 1

有什么想法吗？

Answer 1

你不是在问每个类别的产品总数吗？

SELECT CatID, CategoryName, Parent, COUNT(*) totalProds
FROM categories c
INNER JOIN products p ON p.CatID = c.CatID
GROUP BY CatId

Answer 2

请参见下面的查询

SELECT
    c.`CatID`,
    c.`CategoryName`,
    c.`Parent`,
    COALESCE(COUNT(DISTINCT p.`ProdID`),0) AS totalProds
FROM `CATEGORIES` c
LEFT JOIN `PRODUCTS` p
ON p.`CatID` = c.`CatID`
ORDER BY c.`CatID`

LEFT JOIN为每个类别返回一行。COALESCE，以确保在适当的情况下返回0。

Answer 3

假设您有有限数量的类别级别，您可以一次合并每个级别。以下是4个级别：

SELECT c1.CatID, c1.CategoryName, c1.Parent, COUNT(1) totalProds
FROM products p 
INNER JOIN categories c1 ON c1.CatID = p.CatID
GROUP BY c1.CatID, c1.CategoryName, c1.Parent
UNION
SELECT c2.CatID, c2.CategoryName, c2.Parent, COUNT(1) 
FROM products p 
INNER JOIN categories c1 ON c1.CatID = p.CatID
INNER JOIN categories c2 ON c2.CatID = c1.Parent
GROUP BY c2.CatID, c2.CategoryName, c2.Parent
UNION
SELECT c3.CatID, c3.CategoryName, c3.Parent, COUNT(1) 
FROM products p 
INNER JOIN categories c1 ON c1.CatID = p.CatID
INNER JOIN categories c2 ON c2.CatID = c1.Parent
INNER JOIN categories c3 ON c3.CatID = c2.Parent
GROUP BY c3.CatID, c3.CategoryName, c3.Parent
UNION
SELECT c4.CatID, c4.CategoryName, c4.Parent, COUNT(1) 
FROM products p 
INNER JOIN categories c1 ON c1.CatID = p.CatID
INNER JOIN categories c2 ON c2.CatID = c1.Parent
INNER JOIN categories c3 ON c3.CatID = c2.Parent
INNER JOIN categories c4 ON c4.CatID = c3.Parent
GROUP BY c4.CatID, c4.CategoryName, c4.Parent

希望你明白我的意思。