程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> mysql獲取分組後每組的最大值實例詳解

mysql獲取分組後每組的最大值實例詳解

編輯:MySQL綜合教程

mysql獲取分組後每組的最大值實例詳解。本站提示廣大學習愛好者:(mysql獲取分組後每組的最大值實例詳解)文章只能為提供參考,不一定能成為您想要的結果。以下是mysql獲取分組後每組的最大值實例詳解正文


mysql獲取分組後每組的最大值實例詳解

作者:靈魂之光

這篇文章主要介紹了 mysql獲取分組後每組的最大值實例詳解的相關資料,需要的朋友可以參考下

 mysql獲取分組後每組的最大值實例詳解

1. 測試數據庫表如下:

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. 插入如下數據:

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如下

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. 測試結果如下:

+----+----------+-------+ 
| 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. 網上很多方法都是錯誤的,比如如下一些,親測是不行的

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; 

感謝閱讀,希望能幫助到大家,謝謝大家對本站的支持!

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved