mysql - 通过一次查询获取最后订单日期、订单总数和总金额？

Question

我首先计划使用多个查询和一些 PHP 来完成此操作。

我想做的是获取特定用户上次下订单的时间，该用户的订单总数是多少，以及他支付的总金额/成本/计数是多少。

我尝试过的是这个SQL:

SELECT `orders`.`date_created`, 
SUM(total_count) as total_sum, 
COUNT(id) AS total_orders 
FROM `orders` 
WHERE `user_id` = '96838' 
AND (`status` = 'new' OR `status` = 'delivered') 
ORDER BY `orders`.`date_created` DESC
LIMIT 1

我对上述内容的期望是:

total_sum = total count/amount of all the orders that the user has.
total_orders = total orders
date_created = grab the last orders date_created, so we can know when the last time was.

当我今天运行上述 SQL 时，我确实收到了正确的total_sum 和total_orders 值，但date_created 是错误的(它选择第一个订单而不是最后一个订单？)

“LIMIT 1”有必要吗？

Answer 1

如果我明白你想要什么，你就不需要分组 - 只需获取最大日期即可:

SELECT
    MAX(date_created) as last_order_date, 
    SUM(total_count) as total_sum, 
    COUNT(id) AS total_orders 
FROM
    `orders` 
WHERE
    `user_id` = '96838' 
    AND
    `status` IN ('new', 'delivered')