mysql - SQL查询以获取最近的交易日期

Question

SQL 新手。有人可以告诉我我的 SQL 查询是否正确吗？我试图找出每个独特的客户在他们最近的交易中花了多少钱。

表名:支出

Columns:

1. created_time
2. customer_id
3. spend

SELECT
distinct(customer_id), sum(spend)
FROM
Expenditure
WHERE
created_time =
(SELECT MAX(created_time))
FROM
Expenditure
GROUP BY
distinct(customer_id)

Answer 1

如果您使用的是 MySQL 8+，那么我更喜欢在这里使用窗口函数:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_time DESC) rn
    FROM Expenditure
)

SELECT customer_id, created_time, spend
FROM cte
WHERE rn = 1;

要更正您尝试使用的方法，您可以将子查询与客户的外部查询相关联:

SELECT customer_id, created_time, spend
FROM expenditure e1
WHERE created_time = (SELECT MAX(e2.created_time)
                      FROM Expenditure
                      WHERE e2.customer_id = e1.customer_id);