mysql使用日期分组

-- 按天分组统计每天的订单数量和总金额
SELECT DATE(order_date) AS order_day, COUNT(*) AS order_count, SUM(amount) AS total_amount
FROM orders
GROUP BY order_day
ORDER BY order_day;

-- 按月分组统计每月的订单数量和总金额
SELECT YEAR(order_date) AS order_year, MONTH(order_date) AS order_month, COUNT(*) AS order_count, SUM(amount) AS total_amount
FROM orders
GROUP BY order_year, order_month
ORDER BY order_year, order_month;

-- 按年分组统计每年的订单数量和总金额
SELECT YEAR(order_date) AS order_year, COUNT(*) AS order_count, SUM(amount) AS total_amount
FROM orders
GROUP BY order_year
ORDER BY order_year;

-- 统一时区示例
SELECT DATE(CONVERT_TZ(order_date, '+00:00', '+08:00')) AS order_day, COUNT(*) AS order_count, SUM(amount) AS total_amount
FROM orders
GROUP BY order_day
ORDER BY order_day;

-- 创建索引示例
CREATE INDEX idx_order_date ON orders(order_date);

-- 分页查询示例
SELECT DATE(order_date) AS order_day, COUNT(*) AS order_count, SUM(amount) AS total_amount
FROM orders
GROUP BY order_day
ORDER BY order_day
LIMIT 10 OFFSET 0;