mysql 分组为空补0

CREATE TABLE sales (
    product_id INT,
    amount DECIMAL(10, 2)
);

SELECT product_id, IFNULL(SUM(amount), 0) AS total_sales
FROM sales
GROUP BY product_id;

CREATE TABLE products (
    product_id INT,
    product_name VARCHAR(255)
);

SELECT p.product_id, IFNULL(SUM(s.amount), 0) AS total_sales
FROM products p
LEFT JOIN sales s ON p.product_id = s.product_id
GROUP BY p.product_id;

SELECT COALESCE(product_id, 0) AS product_id, COALESCE(total_sales, 0) AS total_sales
FROM (
    SELECT product_id, SUM(amount) AS total_sales
    FROM sales
    GROUP BY product_id
) subquery
RIGHT JOIN products ON subquery.product_id = products.product_id;