mysql获取分组后每组的最大值实例详解
mysql获取分组后每组的最大值实例详解
1.测试数据库表如下：
createtabletest ( `id`intnotnullauto_increment, `name`varchar(20)notnulldefault'', `score`intnotnulldefault0, primarykey(`id`) )engine=InnoDBCHARSET=UTF8;
2.插入如下数据：
mysql>select*fromtest; +----+----------+-------+ |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如下
selecta.*fromtestainnerjoin(selectname,max(score)scorefromtestgroupbyname)bona. name=b.nameanda.score=b.scoreorderbya.name;
当然，上面的最后的orderbya.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*fromtestorderbyscoredesc)tgroupbynameorderbyscoredesclimit4; selectscore,max(score)fromtestgroupbyname; select*fromtestwherescorein(selectmax(score)fromtestgroupbyname); select*fromtestwherescorein(selectsubstring_index(group_concat(scoreorderbyscoredescseparator','),',',1)fromtestgroupbyname); select*from(selectname,score,ROW_NUMBER()over(groupbynameorderbyscoredesc)asrowNumfromtest)rankwhererank.rowNum<=1orderbyrank.scoredesc; select*from(selectStoresNo,[CustomerCaseNo],[PaymentsTime],ROW_NUMBER()over(partitionbyCustomerCaseNoorderby[PaymentsTime]desc)asrowNum fromBAL_paymentsSwiftInfowhereStoresNo='zq00000034')rankedwhereranked.rowNum<=1orderbyranked.CustomerCaseNo,ranked.PaymentsTimedesc select*from(select*fromtestorderbyscoredesc)asagroupbya.name;
感谢阅读，希望能帮助到大家，谢谢大家对本站的支持！