mysql高效分组求和

CREATE TABLE sales (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT,
    quantity INT,
    sale_date DATE
);

SELECT product_id, SUM(quantity) as total_quantity
FROM sales
GROUP BY product_id;

SELECT product_id, YEAR(sale_date) as year, SUM(quantity) as total_quantity
FROM sales
GROUP BY product_id, YEAR(sale_date);

SELECT product_id, SUM(quantity) as total_quantity
FROM sales
WHERE sale_date BETWEEN '2023-01-01' AND '2023-12-31'
GROUP BY product_id;

SELECT product_id, SUM(COALESCE(quantity, 0)) as total_quantity
FROM sales
GROUP BY product_id;

CREATE INDEX idx_product_id ON sales(product_id);
CREATE INDEX idx_sale_date ON sales(sale_date);