mysql语言分组排序

CREATE TABLE sales (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT,
    category VARCHAR(50),
    amount DECIMAL(10, 2)
);

SELECT category, SUM(amount) AS total_amount
FROM sales
GROUP BY category
ORDER BY total_amount DESC;

SELECT product_id, category, SUM(amount) AS total_amount
FROM sales
GROUP BY product_id, category
ORDER BY total_amount DESC, category ASC;

SELECT category, AVG(amount) AS avg_amount, COUNT(*) AS count
FROM sales
GROUP BY category
ORDER BY avg_amount DESC, count ASC;

SELECT category, SUM(amount) AS total_amount
FROM sales
GROUP BY category
ORDER BY total_amount DESC; -- 确保列名和排序方式正确

SELECT product_id, category, SUM(amount) AS total_amount
FROM sales
GROUP BY product_id, category -- 确保包含所有需要分组的列
ORDER BY total_amount DESC;

-- 创建索引
CREATE INDEX idx_category ON sales(category);

SELECT category, SUM(amount) AS total_amount
FROM sales
GROUP BY category
ORDER BY total_amount DESC;