sql - 如何获取一列中具有最大值的行？

Question

我在 Postgres 中有这个查询:

SELECT DISTINCT P.nome, P.cognome, F.nomeFacolta, F.id, D.orelez
FROM Persona P, Docenza D, InsErogato IE, Facolta F
WHERE D.id_inserogato = IE.id
    AND IE.id_facolta = F.id
    AND D.id_persona = P.id
    AND D.orelez = ANY ( SELECT MAX(D2.orelez)
        FROM Facolta F2, Docenza D2, Inserogato IE2
        WHERE D2.id_inserogato = IE2.id
            AND IE2.id_facolta = F2.id
            AND IE2.annoaccademico = '2009/2010'
        GROUP BY F2.id)
ORDER BY D.orelez DESC;

结果是:

nome            | cognome       | NomeFacolta    | id | orelez |

Francesco         Bortolan        Medicina         7    128000  <---
Giampaolo         Dalle Vedove    Economia         2    98000   <---
Mauro             Spera           Scienze          1    87000   <---
Alessandra        Salomoni        Economia         2    80000
Alessandro        Natucci         Economia         2    80000
Attilio           Boner           Medicina         7    80000
Mario Rosario     Buffelli        Lettere          8    76000   <---
Carlo             Capelli         Lettere          8    72000
etc ...

我想更改或整合我的查询以仅获取 id 的 MAX orelez 值(我用箭头指示了我想保留在结果中的元组).此外，我希望我的查询只采用前三个属性，丢弃最后两个。

这是上述示例所需的输出:

nome            | cognome       | NomeFacolta    

Francesco         Bortolan        Medicina    
Giampaolo         Dalle Vedove    Economia    
Mauro             Spera           Scienze      
Mario Rosario     Buffelli        Lettere     
etc ...

我如何更改或整合我的查询来执行此操作？

Answer 1

您的查询很可能是不正确的。谓词:

WHERE  D.orelez = ANY (SELECT MAX(D2.orelez) ...

将包括恰好具有相同 orelez 的任何行作为任何 id 的最大值，不只是一样id .

您可以修复:

 WHERE (F.id, D.orelez) = ANY (SELECT F2.id, MAX(D2.orelez) ...

但还有更多。只有子查询被限制为 IE2.annoaccademico = '2009/2010' .外部查询没有相同的限制，因此它将返回带有 any annoaccademico 的行，只要orelez匹配。您将不得不重复谓词。 可能您真的想找到:

与 orelez 的最大值匹配的所有行对于任何 id与 annoaccademico = '2009/2010' .

但这似乎不太可能。假设你真的想找到:

每个 id 一行最大 orelez和 annoaccademico = '2009/2010'

这可以用 DISTINCT ON 彻底简化:

SELECT DISTINCT ON (F.id)
       P.nome, P.cognome, F.nomeFacolta
FROM   Persona    P
JOIN   Docenza    D  ON D.id_persona = P.id
JOIN   InsErogato IE ON IE.id = D.id_inserogato
JOIN   Facolta    F  ON F.id = IE.id_facolta
WHERE  IE.annoaccademico = '2009/2010'
ORDER  BY F.id, D.orelez DESC;

关于可能具有 NULL 值的极端情况以及如何打破平局的详细解释和说明:

• Select first row in each GROUP BY group?

调试说明

可能无法产生预期结果的典型原因:

1. 有更多行具有相同的最大值。 D.orelez 中的值. (要全部返回吗？那你需要一个不同的查询。)
2. 最大。 D.orelez 中的值不适用于 annoaccademico = '2009/2010' .
3. 行可能会被连接删除，在 IE 中找不到匹配项或 F .
4. D.orelez 中有 NULL 值- 添加 NULLS LAST就像链接答案中的指示一样。