PostgreSQL MAX 和 GROUP BY

发布于11月11日

我有一张id、year和count的桌子.

我想得到每个id的MAX(count)，并在发生时保留year，因此我提出以下查询:

SELECT id, year, MAX(count)
FROM table
GROUP BY id;

不幸的是，它给了我一个错误:

错误:列"table.year"必须出现在GROUP BY子句中，或者

所以我试着:

SELECT id, year, MAX(count)
FROM table
GROUP BY id, year;

但是，它不会显示MAX(count)，它只是显示表格的原样.我想是因为当按year和id分组时，它得到了该特定年份id的最大值.

那么，我该如何编写这个查询呢？我想拿到id分的MAX(count)分，以及那一年的成绩.

推荐答案

select *
from (
  select id, 
         year,
         thing,
         max(thing) over (partition by id) as max_thing
  from the_table
) t
where thing = max_thing

或者:

select t1.id,
       t1.year,
       t1.thing
from the_table t1
where t1.thing = (select max(t2.thing) 
                  from the_table t2
                  where t2.id = t1.id);

或

select t1.id,
       t1.year,
       t1.thing
from the_table t1
  join ( 
    select id, max(t2.thing) as max_thing
    from the_table t2
    group by id
  ) t on t.id = t1.id and t.max_thing = t1.thing

或 (same as the previous with a different notation)

with max_stuff as (
  select id, max(t2.thing) as max_thing
  from the_table t2
  group by id
) 
select t1.id, 
       t1.year,
       t1.thing
from the_table t1
  join max_stuff t2 
    on t1.id = t2.id 
   and t1.thing = t2.max_thing