mysql groupby多组统计

CREATE TABLE sales (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_category VARCHAR(50),
    sale_date DATE,
    amount DECIMAL(10, 2)
);

SELECT product_category, SUM(amount) AS total_sales
FROM sales
GROUP BY product_category;

SELECT 
    product_category, 
    YEAR(sale_date) AS sale_year, 
    MONTH(sale_date) AS sale_month, 
    SUM(amount) AS total_sales
FROM sales
GROUP BY product_category, sale_year, sale_month;

-- 错误示例
SELECT product_category, sale_date, SUM(amount) AS total_sales
FROM sales
GROUP BY product_category;

-- 正确示例
SELECT product_category, YEAR(sale_date) AS sale_year, MONTH(sale_date) AS sale_month, SUM(amount) AS total_sales
FROM sales
GROUP BY product_category, sale_year, sale_month;

-- 创建索引
CREATE INDEX idx_product_category_sale_date ON sales(product_category, sale_date);