时间戳

首页 > 解决方案 > 如何在子查询中使用 select 语句？（邮政格力）

sql - 如何在子查询中使用 select 语句？（邮政格力）

问题描述

我正在接受面试培训并试图解决一个问题，我想为每个城市找到花费最多的客户。我得到了按城市花费的最大金额的好结果，但是当我尝试检索花费此金额的客户的姓名和姓氏时出现错误。有没有一种有效的方法来做到这一点？谢谢！

select max(total_payment),X.city, X.firstname, X.lastname
from (
select sum(amount) as total_payment, c.customer_id, cit.city_id, cit.city as city, c.first_name as firstname, c.last_name as lastname
from payment p
inner join customer as c on p.customer_id=c.customer_id
inner join address as ad on c.address_id=ad.address_id
inner join city as cit on ad.city_id=cit.city_id
group by c.customer_id, cit.city_id
order by city
) as X
group by X.city

目标结果列：每个城市花费最多的客户的姓名和姓氏。

120，巴黎，尼古拉斯，杜邦

130，马德里，劳尔，加西亚

70，伦敦，戴夫，高盛

标签： sqlpostgresqlsubqueryaggregate-functions

解决方案

你想要窗口功能：

select cc.*
from (select sum(p.amount) as total_payment, c.customer_id, cit.city_id, 
             cit.city as city, c.first_name as firstname, c.last_name as lastname,
             row_number() over (partition by cit.city order by sum(p.amount) desc) as seqnum
      from payment p join
           customer c
           on p.customer_id = c.customer_id join
           address ad
           on c.address_id = ad.address_id join
           city cit
           on ad.city_id = cit.city_id
      group by c.customer_id, cit.city_id
     ) cc
where seqnum = 1;

请注意，您的查询有两个错误应该使任何面试失败：

  1. 您正在ORDER BY子查询中使用。根据标准和大多数数据库，ORDER BY要么不允许要么忽略。
  2. 在您的外部查询中，这些GROUP BY列与未聚合的列不一致SELECT。再一次，这违反了标准，并且大多数数据库返回语法错误。

推荐阅读