id name cost quantity
1 aaa 12 25
2 aaa 13 30
3 bbb 10 10
4 bbb 8 32
按name分组选择quantity最大的记录
结果应该是:
2 aaa 13 30
4 bbb 8 32
请问应该怎么写sql语句,存储过程也可以
1 aaa 12 25
2 aaa 13 30
3 bbb 10 10
4 bbb 8 32
按name分组选择quantity最大的记录
结果应该是:
2 aaa 13 30
4 bbb 8 32
请问应该怎么写sql语句,存储过程也可以
t.*
from
表 t
where
t.id=(select id from 表 where name=t.name order by quantity desc)
a.*
from
表 a,
(select name,max(quantity) as quantity from 表 group by name) b
where
a.name=b.name and a.quantity=b.quantity
where a.name=b.name and a.quantity=b.quantity)
where a.name=b.name and a.quantity=b.quantity)
这样不行么?
1 aaa 12 25
2 aaa 13 30
3 bbb 10 10
4 bbb 8 32
按name分组选择quantity最大的记录
结果应该是:
2 aaa 13 30
4 bbb 8 32
select * from tb as a , (select name , sum(quantity) as quantity from tb group by tb) as b
where a.name = b.name and a.quantity = b.quantity