求mysql语句,请各位大虾指点:
表users中字段
id name moneyto
1 a 500.5
2 b 200
3 c 500
4 d 200
5 a 600.5
6 b 500
7 c 300.5
8 d 100
查出表中moneyto总和最多的用户的name以及与之对应moneyto的总和.
表users中字段
id name moneyto
1 a 500.5
2 b 200
3 c 500
4 d 200
5 a 600.5
6 b 500
7 c 300.5
8 d 100
查出表中moneyto总和最多的用户的name以及与之对应moneyto的总和.
select top 1 * from
(select name,sum(moneyto)as moneyto from users group by name) as #d
order by moneyto desc
如果排序后显示最前一行是语句最后加LIMIT 1或limit 0,1select * from
(select name,sum(moneyto)as moneyto from users group by name) as #d
order by moneyto desc limit 0,1;
+----+------+---------+
| id | name | moneyto |
+----+------+---------+
| 1 | a | 500.500 |
| 2 | b | 200.000 |
| 3 | c | 500.000 |
| 4 | d | 200.000 |
| 5 | a | 600.500 |
| 6 | b | 500.000 |
| 7 | c | 300.500 |
| 8 | d | 100.000 |
+----+------+---------+
8 rows in set (0.00 sec)mysql> select * from (select name,sum(moneyto)as moneyto from users
group by name) as a order by moneyto desc limit 0,1;
+------+----------+
| name | moneyto |
+------+----------+
| a | 1101.000 |
+------+----------+
1 row in set (0.00 sec)
users group by name order by moneyto desc limit 1