mysql怎么查询最大值的相同行数据？

Question

数据表大概是以下这样，表头分别是（id，企业id，客户名称，交易金额）

id	e_id	cus_name	amount
1	1	A	10
2	1	B	50
3	2	C	30
4	2	D	20
5	3	E	10

现在想查询每个企业的最大交易金额的客户，预期结果如下：

1	B
2	C
3	E

现在能想到的一个写法是:

select A.e_id,B.cus_name from (select e_id,max(amount) amt from table group by e_id) A left join table B on A.e_id=B.e_id and A.amt=B.amount

但是感觉比较麻烦，有什么更好的思路吗？

Answer 1

MySQL [wakka]> select a.e_id, b.max_amount from cs as a, (select e_id,max(amount) as max_amount from cs as cs_tmp group by e_id) as b where a.e_id = b.e_id and a.amount = b.max_amount;
+------+------------+
| e_id | max_amount |
+------+------------+
|    1 |         50 |
|    2 |         30 |
|    3 |         10 |
+------+------------+
3 rows in set (0.00 sec)

MySQL [wakka]> select a.e_id, a.cus_name, b.max_amount from cs as a, (select e_id,max(amount) as max_amount from cs as cs_tmp group by e_id) as b where a.e_id = b.e_id and a.amount = b.max_amount;
+------+----------+------------+
| e_id | cus_name | max_amount |
+------+----------+------------+
|    1 | B        |         50 |
|    2 | C        |         30 |
|    3 | E        |         10 |
+------+----------+------------+
3 rows in set (0.00 sec)

做完才发现跟你的方案一样 :(, 貌似常规做法就是这样吧？

Answer 2

select 
  e_id,cus_name
from 
  xxx
where amount in (select max(amount) as am from xxx group by xxx.e_id)

sql 菜鸟写的语句，有点挫。

Answer 3

写不出来的sql我可能就直接在代码里处理了，所有数据搞出来，O(n)就能提取出所有的最大行。