PostgreSQL用MAX运算符获取年度出行次数最多的客户ID

这里提供几种基于你现有子查询的实现方式，按需选择：

方法1：用窗口函数处理并列情况 #

如果存在多个客户年度出行次数并列最多的情况，用RANK()窗口函数可以把这些客户都筛选出来：

WITH cliente_viagens AS (
    SELECT cv.idpessoa, COUNT(cv.idpessoa) AS noviagens
    FROM viagem v
    JOIN clienteviagem cv ON v.idsistema = cv.viagem
    JOIN pessoa p ON p.id = cv.idpessoa
    WHERE EXTRACT(YEAR FROM v.dtviagem) = 2021
    GROUP BY cv.idpessoa
)
SELECT idpessoa
FROM (
    SELECT idpessoa, noviagens,
           RANK() OVER (ORDER BY noviagens DESC) AS ranking
    FROM cliente_viagens
) ranked
WHERE ranking = 1;

说明：如果只想要任意一个次数最多的客户，把RANK()换成ROW_NUMBER()即可，但这种写法会忽略并列的情况。

方法2：通过最大次数匹配 #

先算出年度最大出行次数，再匹配对应的客户ID：

SELECT cv.idpessoa
FROM viagem v
JOIN clienteviagem cv ON v.idsistema = cv.viagem
JOIN pessoa p ON p.id = cv.idpessoa
WHERE EXTRACT(YEAR FROM v.dtviagem) = 2021
GROUP BY cv.idpessoa
HAVING COUNT(cv.idpessoa) = (
    SELECT MAX(noviagens)
    FROM (
        SELECT COUNT(cv.idpessoa) AS noviagens
        FROM viagem v
        JOIN clienteviagem cv ON v.idsistema = cv.viagem
        JOIN pessoa p ON p.id = cv.idpessoa
        WHERE EXTRACT(YEAR FROM v.dtviagem) = 2021
        GROUP BY cv.idpessoa
    ) sub
);

说明：这种写法和你的原始子查询结构贴合，同样支持输出所有并列次数最多的客户。

另外提一句：你原来的SQL用的是隐式连接，改成显式JOIN语法会让逻辑更清晰、可读性更强，上面的示例已经帮你做了调整。

内容的提问来源于stack exchange，提问作者Kripthonite