mysql几个计数和联接返回奇怪的值

Question

我正试图使用JOIN在几个表中进行计数，但当我做了几个JOINs时，COUNT的计数却被错误地计算了。

基本上我有四张桌子，名字是：

1. predective_search
2. predective_to_product
3. predective_to_category
4. predective_to_manufacturer

我想数一下predective_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