sql同一ID的最大值

select t1.id,t1.name,t1.value
from test t1,test t2
where t1.id=t2.id
group by t1.id,t1.name,t1.value
having t1.value=max(t2.value)

select id,name,max(value) value from test group by id,name

有简单的还用麻烦的，本来不用关联就能出来的结果，你非要关联出结果，影响效率不说，还容易出错，你自己在你机器运行一下不就知道了吗

name字段是取较小的值吧？

select id,min(name) as name,max([value]) as [value]
from test
group by id

不允许用子查询,即嵌套select
为什么不用呢

很简单啊

select id,name,max(value)
from test
group by id,name
order by 1,2;

oracle的话用分析函数也可以，不过有点多此一举了
select distinct id,name,max(value) over(partition by id) "所在组的最大值"
from test;

select id,name,value "自己的值",max(value) over(partition by id) "所在组的最大值"
from test;