mysql from子句中的嵌套查询问题

Question

数据库表的结构如下：
customer: custkey name comment
orders:orderkey custkey totalprice

求查询订单平均金额超过 25 万元的顾客中的中国籍顾客信息
代码如下：
SELECT *
FROM (SELECT *
FROM customer
WHERE name in(SELECT name FROM
(SELECT name,AVG(totalprice)
FROM customer join orders using(custkey) as a
GROUP BY name
HAVING AVG(totalprice)>250000)))
WHERE nationkey=40

结果报错，求问原因

Answer 1

SELECT T0.custkey, T0.name, T0.comment FROM 
customer T0 JOIN
(
SELECT a.name,AVG(b.totalprice) FROM customer a join orders b on a.custkey = b.custkey
GROUP BY a.name
HAVING AVG(b.totalprice)>250000)T1
ON T0.name = T1.name
WHERE nationkey=40