假设订单表为 orders,用户表为 users,订单日期存在 order_date 字段中,订单金额存在 order_amount 字段中,用户ID存在 user_id 字段中,查询语句如下:
SELECT users.user_id, SUM(orders.order_amount) as total_amount
FROM orders
JOIN users ON orders.user_id = users.user_id
WHERE orders.order_date BETWEEN '2021-01-01' AND '2021-12-31'
GROUP BY users.user_id;
解释:
SELECT users.user_id
: 查询结果需要包括用户ID。SUM(orders.order_amount) as total_amount
: 计算每个用户在指定日期范围内的订单总额,并将计算结果命名为 total_amount。FROM orders JOIN users ON orders.user_id = users.user_id
: 将订单表和用户表联结起来,以便查询用户信息。WHERE orders.order_date BETWEEN '2021-01-01' AND '2021-12-31'
: 限制查询订单日期在指定范围内。GROUP BY users.user_id
: 按照用户分组,以便计算每个用户的订单总额。