求带有聚合函数的一句sql语句
比如某个表test里有字段name, amount, age, balance
对于每个name，我要把amount最大值所在的行找出来。
希望能用一条sql语句实现。我现在想到的语句如下：
select a.name, amount, age, balance
from (select name, max(amount) as max from test group by name) as a, test
where a.name=test.name and a.max=test.amount
有没有别的方法？比如子查询
[解决办法]
参考下贴，N=1
http://topic.csdn.net/u/20091231/16/2f268740-391e-40f2-a15e-f243b2c925ab.html
分组取最大N条记录方法征集
[解决办法]
你用ASA？参考
select * from (select id,gid,col1,col2, ROW_NUMBER( ) over(partition by gid order by col2) as bz from t2v group by id,gid,col1,col2) a
where bz <=3
还可以用HAVING、
SELECT a.id,a.gid,a.col1,a.col2 FROM t2v a
WHERE 3>=(
SELECT COUNT(*) FROM t2v b
WHERE a.gid=b.gid AND a.col2 <=b.col2)
ORDER BY a.gid,a.col2 desc
这一类的SQL
[解决办法]
select * from (select id,gid,col1,col2, ROW_NUMBER( ) over(partition by gid order by col2) as bz from t2v group by id,gid,col1,col2) a
where bz <=3
