http://blog.csdn.net/magicharvey/article/details/21372813
- mysql> select * from test;
- +----+-------+------+-------+
- | id | name | age | class |
- +----+-------+------+-------+
- | 1 | qiu | 22 | 1 |
- | 2 | liu | 42 | 1 |
- | 4 | zheng | 20 | 2 |
- | 3 | qian | 20 | 2 |
- | 0 | wang | 11 | 3 |
- | 6 | li | 33 | 3 |
- +----+-------+------+-------+
- 6 rows in set (0.00 sec)
如果想找到每个class里面的最大的age,则需要使用group by和max。
如下的sql语句,则输出结果有错误:
- mysql> select id,name,max(age),class from test group by class;
- +----+-------+----------+-------+
- | id | name | max(age) | class |
- +----+-------+----------+-------+
- | 1 | qiu | 42 | 1 |
- | 4 | zheng | 20 | 2 |
- | 0 | wang | 33 | 3 |
- +----+-------+----------+-------+
- 3 rows in set (0.00 sec)
如果我使用以下的语句进行查找,则可以返回真实的结果。
- mysql> select * from (
- -> select * from test order by age desc) as b
- -> group by class;
- +----+-------+------+-------+
- | id | name | age | class |
- +----+-------+------+-------+
- | 2 | liu | 42 | 1 |
- | 4 | zheng | 20 | 2 |
- | 6 | li | 33 | 3 |
- +----+-------+------+-------+
- 3 rows in set (0.00 sec)
方法2:
- select * from test t where t.age = (select max(age) from test where t.class = class) order by class;