mysql 多次计数和连接返回奇怪的值

Question

我尝试使用 JOIN 在多个表中进行计数，但是当我进行多个 JOIN 时，COUNT 出现错误已统计。

基本上我有 4 个表，名称为:

1. predictive_search
2. predictive_to_product
3. predictive_to_category
4. predictive_to_manufacturer

我想统计表predecive_search中具有相同id的产品、类别和制造商的总数。

这是我的代码:

SELECT * , 
    COUNT(pp.predictive_id) AS total_products, 
    COUNT(pc.predictive_id) AS total_categories, 
    COUNT(pm.predictive_id) AS total_manufacturers 
FROM predictive_search ps 
LEFT JOIN predictive_to_product pp ON (ps.predictive_id = pp.predictive_id) 
LEFT JOIN predictive_to_category pu ON (ps.predictive_id = pc.predictive_id) 
LEFT JOIN oc_predictive_to_manufacturer pm ON (ps.predictive_id = pm.predictive_id)    
GROUP BY ps.predictive_id

我认为还需要GROUP BY。我陷入困境，因为我没有任何办法做到这一点

Answer 1

SELECT 
    ps.*,
    agg_pp.total_products,
    agg_pc.total_categories,
    agg_pm.total_manufacturers
FROM predictive_search ps 
LEFT JOIN ( 
        SELECT pp.predictive_id, COUNT(*) AS total_products
        FROM predictive_to_product pp 
        GROUP BY pp.predictive_id 
    ) agg_pp ON ps.predictive_id = agg_pp.predictive_id
LEFT JOIN ( 
        SELECT pc.predictive_id, COUNT(*) AS total_categories
        FROM predictive_to_category pc 
        GROUP BY pc.predictive_id 
    ) agg_pc ON ps.predictive_id = agg_pc.predictive_id
LEFT JOIN ( 
        SELECT pm.predictive_id, COUNT(*) AS total_manufacturers
        FROM predictive_to_category pm 
        GROUP BY pm.predictive_id 
    ) agg_pm ON ps.predictive_id = agg_pm.predictive_id