查询每个商品的好评总数

SELECT
    *, IFNULL(t.koubei, 0) AS good_koubei
FROM
    (
        SELECT
            *
        FROM
            (
                SELECT
                    i.id AS aid,
                    i.*, cb. NAME AS top_category_name,
                    cb.id AS top_category_id,
                    ca. NAME AS secend_category_name,
                    ca.id AS secend_category_id,
                    ib. NAME AS brand_name,
                    sale_mode.pic AS country_pic
                FROM
                    item AS i
                LEFT JOIN item_category AS ca ON ca.id = i.category_id
                LEFT JOIN item_category AS cb ON ca.parent_id = cb.id
                LEFT JOIN item_brand AS ib ON i.brand_id = ib.id
                LEFT JOIN sale_mode ON i.country_id = sale_mode.country_id
                WHERE
                    i.id IN (198309584, 198309582)
            ) a
        LEFT JOIN (
            SELECT
                i.id AS bid,
                count(k.score) AS koubei
            FROM
                item AS i
            LEFT JOIN mia_group_test.koubei AS k ON i.id = k.item_id
            WHERE
                i.id IN (198309584, 198309582)
            AND k.score > 4
            GROUP BY
                i.id
        ) b ON a.aid = b.bid
    ) AS t

1.计算需要的表头

2.分组过滤

3.left join合并结果

转载于:https://www.cnblogs.com/dannywang/p/5950423.html