PostgreSQL MAX和GROUP BY
发布时间:2020-12-13 16:28:55 所属栏目:百科 来源:网络整理
导读:我有一个id,年和数的表. 我想得到每个id的MAX(count),并保持发生的一年,所以我做这个查询: SELECT id,year,MAX(count)FROM tableGROUP BY id; 不幸的是,它给我一个错误: ERROR: column “table.year” must appear in the GROUP BY clause or be used in a
我有一个id,年和数的表. 我想得到每个id的MAX(count),并保持发生的一年,所以我做这个查询: SELECT id,year,MAX(count) FROM table GROUP BY id; 不幸的是,它给我一个错误:
所以我试试: SELECT id,MAX(count) FROM table GROUP BY id,year; 但是,它不做MAX(计数),它只是显示表.我想,因为当按年份和身份分组时,它会获得该特定年份的最大值. 那么,怎么写这个查询呢?我想得到ID的MAX(计数)和发生的那一年. select * from ( select id,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.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 或(与以前的不同符号相同) with max_stuff as ( select id,max(t2.thing) as max_thing from the_table t2 group by id ) select t1.id,t1.thing from the_table t1 join max_stuff t2 on t1.id = t2.id and t1.thing = t2.max_thing (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |