sql - Postgresql 子查询包含错误

Question

我正在学习 SQL 课程，这是我的练习。我把它分成两个步骤（内部和外部声明）。在第三步中，我想将前面的步骤组合成一个带有子查询的查询。但它不起作用。有人可以帮我解决这个问题（甚至可能让我理解这个..）吗？太感谢了！

练习：[您的团队想知道支付最高总金额的前 5 名客户列表中支付的平均金额是多少] 支付最高总金额的前 5 名客户已经使用此查询进行了查询：

SELECT B.customer_id, B.first_name, B.last_name, D.city, E.country,
SUM(amount)  AS total_payment 
FROM payment A
INNER JOIN customer B ON A.customer_id = B.customer_id
INNER JOIN address C ON B.address_id = C.address_id
INNER JOIN city D ON C.city_id = D.city_id
INNER JOIN country E ON D.country_id=E.country_id
WHERE D.city IN ('Aurora', 'Atlixco', 'Xintai','Adoni', 'Dhule (Dhulia)', 'Kurashiki', 'Pingxiang', 'Sivas', 'Celaya', 'So Leopoldo') 
GROUP BY B.customer_id, B.first_name, B.last_name, D.city, E.country

这应该做成一个子查询。它应该有别名 total_amount_paid。写一个外部语句来计算平均支付金额。将您的子查询添加到外部语句。它将出现在 SELECT、WHERE 或 FROM 子句中。（提示：在外部语句中引用子查询时，请确保使用子查询的别名“total_amount_paid”。）

第一步（内部语句）

(SELECT B.customer_id, B.first_name, B.last_name, D.city, E.country,
SUM(amount)  AS total_payment 
FROM payment A
INNER JOIN customer B ON A.customer_id = B.customer_id
INNER JOIN address C ON B.address_id = C.address_id
INNER JOIN city D ON C.city_id = D.city_id
INNER JOIN country E ON D.country_id=E.country_id
WHERE D.city IN ('Aurora', 'Atlixco', 'Xintai','Adoni', 'Dhule (Dhulia)', 'Kurashiki', 'Pingxiang', 'Sivas', 'Celaya', 'So Leopoldo') 
GROUP BY B.customer_id, B.first_name, B.last_name, D.city, E.country) AS total_amount_paid

第二步（外部声明）

SELECT B.customer_id, B.first_name, B.last_name, D.city, E.country,
AVG(total_amount_paid) AS avg_amount_paid
GROUP BY avg_amount_paid

第三步（组合查询，出现了很多我无法修复的错误，因为我不明白我在做什么，基本上）

SELECT AVG (total_amount_paid) as average.total_amount_paid
FROM
(SELECT B.customer_id, B.first_name, B.last_name, D.city, E.country,
SUM(amount)  AS total_payment 
FROM payment A
INNER JOIN customer B ON A.customer_id = B.customer_id
INNER JOIN address C ON B.address_id = C.address_id
INNER JOIN city D ON C.city_id = D.city_id
INNER JOIN country E ON D.country_id=E.country_id
WHERE D.city IN ('Aurora', 'Atlixco', 'Xintai','Adoni', 'Dhule (Dhulia)', 'Kurashiki', 'Pingxiang', 'Sivas', 'Celaya', 'So Leopoldo') 
GROUP BY B.customer_id, B.first_name, B.last_name, D.city, E.country) AS total_amount_paid
GROUP BY average.total_amount_paid

由于 GROUP BY 语句，它可能也是一团糟？老实说，我什至不明白 GROUP BY 是什么意思.. :D

标签： sqlpostgresqlsubquery