1. 测试数据库表如下:
1
2
3
4
5
6
7
|
create table test
(
`id` int not null auto_increment,
` name ` varchar (20) not null default '' ,
`score` int not null default 0,
primary key (`id`)
)engine=InnoDB CHARSET=UTF8;
|
2. 插入如下数据:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
|
mysql> select * from test;
+ ----+----------+-------+
| id | name | score |
+ ----+----------+-------+
| 1 | jason | 1 |
| 2 | jason | 2 |
| 3 | jason | 3 |
| 4 | linjie | 1 |
| 5 | linjie | 2 |
| 6 | linjie | 3 |
| 7 | xiaodeng | 1 |
| 8 | xiaodeng | 2 |
| 9 | xiaodeng | 3 |
| 10 | hust | 2 |
| 11 | hust | 3 |
| 12 | hust | 1 |
| 13 | haha | 1 |
| 14 | haha | 2 |
| 15 | dengzi | 3 |
| 16 | dengzi | 4 |
| 17 | dengzi | 5 |
| 18 | shazi | 3 |
| 19 | shazi | 4 |
| 20 | shazi | 2 |
+ ----+----------+-------+
|
3. 下面是重点,目的是要按照name分组,然后分组后,获取每组中score分数最多的,sql如下
1
2
|
select a.* from test a inner join ( select name , max (score) score from test group by name )b on a.
name =b. name and a.score=b.score order by a. name ;
|
当然,上面的最后的order by a.name可以去掉
4. 测试结果如下:
1
2
3
4
5
6
7
8
9
10
11
|
+ ----+----------+-------+
| id | name | score |
+ ----+----------+-------+
| 3 | jason | 3 |
| 6 | linjie | 3 |
| 9 | xiaodeng | 3 |
| 11 | hust | 3 |
| 14 | haha | 2 |
| 17 | dengzi | 5 |
| 19 | shazi | 4 |
+ ----+----------+-------+
|
5. 网上很多方法都是错误的,比如如下一些,亲测是不行的
1
2
3
4
5
6
7
8
9
10
11
|
select * from ( select * from test order by score desc ) t group by name order by score desc limit 4;
select score, max (score) from test group by name ;
select * from test where score in ( select max (score) from test group by name );
select * from test where score in ( select substring_index(group_concat(score order by score desc separator ',' ), ',' ,1) from test group by name );
select * from ( select name ,score,ROW_NUMBER() over( group by name order by score desc ) as rowNum from test) rank where rank.rowNum <=1 order by rank.score desc ;
select * from ( select StoresNo,[CustomerCaseNo],[PaymentsTime], ROW_NUMBER() over(partition by CustomerCaseNo order by [PaymentsTime] desc ) as rowNum
from BAL_paymentsSwiftInfo where StoresNo= 'zq00000034' ) ranked where ranked.rowNum <= 1 order by ranked.CustomerCaseNo, ranked.PaymentsTime desc
select * from ( select * from test order by score desc ) as a group by a. name ;
|
感谢阅读,希望能帮助到大家,谢谢大家对本站的支持!
原文链接:http://blog.csdn.net/u011734144/article/details/51982134