Question

如何取group by后最大值所对应的字符字段?

表:test
id name num type
1 ddd 20 1
3 aaa 13 2
5 ccc 24 4
7 aee 30 2

select type,max(num),最大num所对应的名称和ID from test group type

需要查询的字段:最大num所对应的名称和ID

请指教 数据库为:sqlserver

Answer 1

你提的有点不太清楚，或许我理解问题 ，你要显示type这一列吗？是考虑type这一列的数据查找最大的num再查询对应的名称和ID，还是不考虑type这一列，直接最大的num
1、不考虑，如下
select name,id from test a where a.num=(select max(b.num) from test b )
2、考虑，如下
select name,id,type from test a where a.num=( select max(b.num) from test b where a.type=b.type group by type )

Answer 2

select a.id,a.name,b.type,b.max_num
from test a,(
select type,max(num) as max_num
from test
group by type)as b
where a.type=b.type and a.num=b.max_num

Answer 3

select name, id
from test t
where not exists (select 1 from test where num > t.num)

或者
select name, id from test where num = (select max(num) from test)