sql - 如何从 PostgresQL 中的 MAX() 聚合函数获取两个值

Question

现在我有一个查询，我将对其使用 MAX() 聚合函数。我做到了

使用 t2 AS(
选择 customerid , sum(total) sumTotal
从发票
按 1 分组
按 1 订购
)
选择 c.customerid customerid，c.firstName firstName，c.lastName lastName，
c.country customerCountry , MAX(总和) MaxSumTotal
FROM 客户 c JOIN t2
ON c.customerid = t2.customerid
按 4 分组
按 4 排序

但是那个只为 MAX() 返回一个值。我有一个有两个相等的 MAX() 输出。我想展示他们两个

我该怎么做？？？

这是数据库的ERD图

Answer 1

使用rank():

WITH t2 AS (
      SELECT customerid , sum(total) as sumTotal,
             RANK() OVER (ORDER BY sum(total) DESC) as seqnum
      FROM invoice
      GROUP BY 1
     )
SELECT c.customerid, c.firstName firstName, c.lastName lastName,
       c.country as customerCountry, sumTotal as MaxSumTotal
FROM customer c JOIN
     t2
     ON c.customerid = t2.customerid
WHERE seqnum = 1
ORDER BY c.customerid;