我有一张桌子id,year和count。 ' N4 K8 Q6 Y. N" O我想MAX(count)为每个获取id并保留year发生时间如下: ! t5 `3 ^# _! \) }& m2 bSELECT id,year,MAX(count)FROM tableGROUP BY id;不幸的是,这给了我一个错误:9 |* |$ a. R$ x, w* K6 R2 q8 ~ 错误:“ table.year必须出现列GROUP BY子句中或在聚合函数中使用' J+ Z9 [/ M9 j 所以我试了: ; L9 S" o4 y: X& f0 a) S4 \7 SSELECT id,year,MAX(count)FROM tableGROUP BY id,year;然后,它不执行任何操作MAX(count),只按原样显示表。我猜是因为按year和分组时id,它将获得该id特定年份的最大值。 + ^, o5 ^; E/ f j那么,如何编写查询呢?我想得到它id麓小号MAX(count)这种情况发生在当年。2 G! m- ? [' N
5 T' Y h& i, ?" L解决方案: * I# j9 g+ p- r F5 X select *from ( select id, year, thing, max(thing) over (partition by id) as max_thing from the_table) twhere thing = max_thing或者:+ b. ^& I, e" L% w9 p% J
select t1.id, t1.year, t1.thingfrom the_table t1where t1.thing = (select max(t2.thing) from the_table t2 where t2.id = t1.id);或者6 x4 @0 g) l' B* ^/ K& Q* n
select t1.id, t1.year, t1.thingfrom 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或者(与前者相同,但符号不同)+ n' I; j8 ^; Y7 `( |9 @1 \
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.thingfrom the_table t1 join max_stuff t2 on t1.id = t2.id and t1.thing = t2.max_thing