题解 | 电商平台需要对商品的销售和评价情况进行综合分析

SELECT 
    p.product_id,
    p.product_name,
    s.total_quantity,
    r.average_rating
FROM 
    products_underline p
JOIN (
    SELECT 
        product_id,
        SUM(quantity) AS total_quantity
    FROM sales_underline
    GROUP BY product_id
) s ON p.product_id = s.product_id
JOIN (
    SELECT 
        product_id,
        ROUND(AVG(rating), 2) AS average_rating
    FROM reviews_underline
    GROUP BY product_id
) r ON p.product_id = r.product_id
WHERE r.average_rating < 4
ORDER BY r.average_rating ASC, p.product_id ASC;

避免笛卡尔积 使用表关联