mysql 数据分类查询

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

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

SELECT product_category, SUM(sale_amount) AS total_sales
FROM sales
WHERE sale_date BETWEEN '2023-01-01' AND '2023-12-31'
GROUP BY product_category;

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

SELECT product_category, COUNT(*) AS sale_count, AVG(sale_amount) AS avg_sale_amount
FROM sales
GROUP BY product_category;

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

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

-- 创建索引
CREATE INDEX idx_product_category ON sales(product_category);
CREATE INDEX idx_sale_date ON sales(sale_date);

-- 使用 EXPLAIN 分析查询
EXPLAIN SELECT product_category, SUM(sale_amount) AS total_sales
FROM sales
GROUP BY product_category;