postgrei如何使值不重复在SQL GROUP BY当我添加日期?

Question

我最近开始学习SQL，我有点困惑。我想应用这个查询，但是要添加一个日期列:

SELECT CONCAT(first_name, ' ', last_name) AS Full_name,
SUM(p.amount) Pay_amount
FROM customer c
JOIN payment p ON c.customer_id = p.customer_id
GROUP BY 1
ORDER BY 2 DESC
LIMIT 10;

但是每当我添加日期列时，结果就完全改变为具有不同日期的重复客户。

SELECT CONCAT(first_name, ' ', last_name) AS Full_name,
SUM(p.amount) Pay_amount, 
payment_date
FROM customer c
JOIN payment p ON c.customer_id = p.customer_id
GROUP BY 1, 3
ORDER BY 2 DESC
LIMIT 10;

编辑:这是我使用的样本数据库:https://www.postgresqltutorial.com/postgresql-sample-database/

Answer 1

您可以使用聚合函数:

SELECT CONCAT(first_name, ' ', last_name) AS Full_name,
SUM(p.amount) AS Pay_amount, 
MAX(p.payment_date)
FROM customer c JOIN
payment p
ON c.customer_id = p.customer_id
GROUP BY 1
ORDER BY 2 DESC
LIMIT 10;

Answer 2

我找到了解决方案。谢谢,每一个人。我意识到我的问题很愚蠢。