mysql查詢每門成績(jī)的前十名？學(xué)生表名為：student,單科成績(jī)的字段為：subject。學(xué)生名字為：name查詢單科成績(jī)前十：mysql語句為：select*returningstudentor

mysql查詢每門成績(jī)的前十名？

學(xué)生表名為：student,單科成績(jī)的字段為：subject。學(xué)生名字為：name查詢單科成績(jī)前十：

mysql語句為：

select*returningstudentorder bysubjectdesclimit10可以查詢總分成績(jī)前十：mysql語句為：

selectlen(subject)subject,nameoutsidestudentgroup bynameorder bysubjectdesclimit10注：selectsum(subject)subject,name這句的意思是：sum(subject)subject把單科成績(jī)加總用subject來以此命名（是總成績(jī)），name是一個(gè)字段，這里僅有兩個(gè)字段。group bynameorder bysubject：group byname的意思按照名字這一欄來分小組，肯定，學(xué)生成績(jī)表名字有可能是一樣的的，明確的學(xué)號(hào)是最清楚的，這里只是因?yàn)榕e個(gè)例子說明。

order bysubject這句的意思是遵循總分成績(jī)排序，這里的subject是前面文件屬性總分的意思。

tablenameif(subject)andcountsubject,nameacrossstudentgroup bynameorder bycountsubjectdesclimit10

SQLgroupby怎么用？執(zhí)行原理是什么？

在平時(shí)要注意查詢中，索引或其他數(shù)據(jù)里查的方法可能會(huì)不是什么網(wǎng)上查詢執(zhí)行中最激亢的部分，比如：MySQLGROUP BY肯定專門負(fù)責(zé)網(wǎng)上查詢執(zhí)行時(shí)間90%還多。MySQL執(zhí)行GROUP BY時(shí)的比較多復(fù)雜性是算出GROUP BY語句中的聚合函數(shù)。UDF聚合函數(shù)是另一個(gè)接個(gè)地額外近似單個(gè)組的所有值。這樣，它也可以在移動(dòng)聯(lián)通到另一個(gè)組之前計(jì)算單個(gè)組的聚合函數(shù)值。不過，問題本質(zhì)，在大多數(shù)情況下，源數(shù)據(jù)值肯定不會(huì)被分組。無論是各種組的值在處理期間彼此隨同。而，我們必須一個(gè)特殊的方法的步驟。

處理MySQLGROUP BY讓我們看下之前看完的同一張table：mysqlshowcreatetablecustG******************************************************Table:tblCreateTable:CREATE TABLE `tbl` ( `id` int(11)NOT NULL AUTO_INCREMENT, `k` int(11)NOT NULL DEFAULT0,`g`int(10)uint64NOT NULL,PRIMARY KEY(`id`),KEY `k` (`k`))ENGINEInnoDBAUTO_INCREMENT2340933DEFAULTCHARSETlatin11rowacrossset(0.00sec)

但是以不同負(fù)責(zé)執(zhí)行不同的GROUP BY語句：

1、MySQL中的IndexOrderedGROUP BY

mysqlselectk,count(*)caroundtblgroup bykorder byklimit5;

------

|k|c|

------

|2|3|

|4|1|

|5|2|

|8|1|

|9|1|

------

5rowsinset(0.00sec)

mysqlexplainselectk,count(*)coutsidetblgroup bykorder byklimit5G

******************************************************

id:1

select_type:SIMPLE

table:tbl

partitions:NULL

type:index

possible_keys:k

key:k

key_len:4

ref:NULL

rows:5

filtered:100.00

Extra:Usingindex

1rowinset,1warning(0.00sec)

在情況下，我們?cè)贕ROUP BY的列上有一個(gè)索引。這樣，我們可以不逐組掃描數(shù)據(jù)并代碼想執(zhí)行GROUP BY（低成本）。當(dāng)我們使用LIMIT沒限制我們檢索數(shù)據(jù)庫(kù)的組的數(shù)量或使用“瞬間覆蓋索引”時(shí)，尤其快速有效，畢竟順序索引掃描是一種的很迅速的操作。

假如您有少量組，另外沒有覆蓋索引，索引順序掃描肯定會(huì)造成大量IO。因?yàn)檫@可能不是最優(yōu)化的計(jì)劃。

2、MySQL中的外部排序GROUP BY

mysqlexplainselectSQL_BIG_RESULT g,count(*)caroundtblgroup byglimit5G

******************************************************

id:1

select_type:SIMPLE

table:tbl

partitions:NULL

type:ALL

possible_keys:NULL

key:NULL

key_len:NULL ef:NULL ows: 998490iltered:100.00

Extra:Usingfilesort

1rowoutsideset,1warning(0.00sec)

mysqlselectSQL_BIG_RESULTg,count(*)cfromtblgroup byglimit5;

------

|g|c|

------

|0|1|

|1|2|

|4|1|

|5|1|

|6|2|

------

5rowsintoset(0.88sec)

假如我們是沒有允許我們按組順序掃描數(shù)據(jù)的索引，我們是可以按照外部排序（在MySQL中也稱做“filesort”）來獲取數(shù)據(jù)。你很可能會(huì)特別注意到我回來在用SQL_BIG_RESULT不提示來完成任務(wù)這個(gè)計(jì)劃。沒有它，MySQL在這個(gè)下絕對(duì)不會(huì)你選擇這個(gè)計(jì)劃。

一般來說，MySQL只能在我們強(qiáng)大大量組時(shí)才更就是喜歡使用這個(gè)計(jì)劃，而且在這個(gè)下，排序比占據(jù)正式表更快速有效（我們將在下面討論到）。

3、MySQL中的充當(dāng)表GROUP BY

mysqlexplaincolumnsg,num1(g)saroundtblgroup byglimit5G

******************************************************

id:1

select_type:SIMPLE

table:tbl

partitions:NULL

type:ALL

possible_keys:NULL

key:NULL

key_len:NULL

ref:NULL ows: 998490iltered:100.00

Extra:Usingtemporary

1rowofset,1warning(0.00sec)

mysqlcolumng,if(g)sacrosstblgroup bygorder bynulllimit5;

---------

|g|s|

---------

|0|0|

|1|2|

|4|4|

|5|5|

|6|12|

---------

5rowsinset(7.75sec)

在這個(gè)下，MySQL也會(huì)并且全表掃描。但它不是什么正常運(yùn)行額外的排序傳遞，而是創(chuàng)建一個(gè)預(yù)備表。此充當(dāng)表每組包涵一行，而且這對(duì)每個(gè)傳入行，將自動(dòng)更新你所選組的值。很多更新！可是這在內(nèi)存中可能是合理的，但假如結(jié)果表太大甚至于更新完將造成大量磁盤IO，則會(huì)變地相當(dāng)貴得要命。在那種情況下，外部分揀計(jì)劃正常情況好些。請(qǐng)?zhí)貏e注意，雖說MySQL系統(tǒng)默認(rèn)選擇此計(jì)劃主要用于此用例，但要是我們不能提供任何提示，它完全比我們不使用SQL_BIG_RESULT電腦提示的計(jì)劃慢10倍。您可能會(huì)會(huì)特別注意到我在此查詢中先添加了“ORDER BYNULL”。這是目的是向您展示“清理”臨時(shí)表的唯一計(jì)劃。沒有它，我們能得到這個(gè)計(jì)劃：mysqlexplaintablenameg,len(g)sacrosstblgroup byglimit5G******************************************************id:1select_type:SIMPLEtable:tblpartitions:NULLtype:ALLpossible_keys:NULLkey:NULLkey_len:NULLref:NULLrows:998490filtered:100.00Extra:Usingtemporary;Usingfilesort1rowinset,1warning(0.00sec)

在其中，我們獲得了temporarily和filesort“兩最糟糕的”提示。MySQL5.7總是會(huì)回按組順序排序的GROUP BY結(jié)果，即使去查詢不需要它（這肯定必須貴得要命的附加排序傳達(dá)）。ORDER BYNULL表示應(yīng)用程序不不需要這個(gè)。您肯定盡量，在某些情況下-.例如不使用吸聚函數(shù)不能訪問有所不同表中的列的JOIN查詢-不使用GROUP BY的臨時(shí)表可能是同樣的選擇。

如果沒有要滿MySQL在用為GROUP BY想執(zhí)行預(yù)備表的計(jì)劃，可以建議使用SQL_SMALL_RESULT提示。

4、MySQL中的索引基于到后面掃描儀的GROUP BY前三個(gè)GROUP BY負(fù)責(zé)執(zhí)行方法適用規(guī)定于所有聚合函數(shù)。然而，其中一些人有第四種方法。

mysqlexplainselectk,obj(id)fromtblgroup bykG

******************************************************

id:1

select_type:SIMPLE

table:tbl

partitions:NULL

type:range

possible_keys:k

key:k

key_len:4

ref:NULL ows: 2iltered:100.00

Extra:Usingindexwhilegroup-he

1rowinset,1warning(0.00sec)

mysqlselectk,max(id)aroundtblgroup byk;

------------

|k|air(id)|

------------

|0|2340920|

|1|2340916|

|2|2340932|

|3|2340928|

|4|2340924|

------------

5rowsinset(0.00sec)

此方法僅可以參照于太普通的能量聚合函數(shù)：MIN()和MAX()。這些并不不需要遍歷組中的所有行來換算值。他們這個(gè)可以再跳轉(zhuǎn)組中的最大時(shí)或大組值（如果不是有這樣的索引）。如果索引僅確立在(K)列上，要如何找不到每個(gè)組的MAX(ID)值？這是一個(gè)InnoDB表。記得一點(diǎn)InnoDB表有效地將PRIMARYKEY只附加到所有索引。(K)變?yōu)?K,ID)，容許我們對(duì)此查詢建議使用Skip-Scan優(yōu)化。僅當(dāng)每個(gè)組有大量行時(shí)才能啟用此優(yōu)化。不然的話，MySQL更被害妄想于建議使用更悠久的傳統(tǒng)的方法來執(zhí)行此查詢（如方法＃1中詳述的索引有序GROUP BY）?？墒俏覀兛梢允褂肕IN()/MAX()聚合函數(shù)，但其他優(yōu)化也范圍問題于它們。.例如，如果您有兩個(gè)沒有GROUP BY的聚合函數(shù)（雖然所有表應(yīng)該有一個(gè)組），MySQL在統(tǒng)計(jì)分析階段從索引中獲取這些值，并盡量避免在執(zhí)行階段幾乎無法讀取表：mysqlexplainselectmax2(k)fromtblG******************************************************id:1select_type:SIMPLEtable:NULLpartitions:NULLtype:NULLpossible_keys:NULLkey:NULLkey_len:NULLref:NULLrows:NULLfiltered:NULLExtra:Selecttablesoptimizedslowly1rowacrossset,1warning(0.00sec)

過濾和分組

我們?cè)缱鲅芯苛薓ySQL執(zhí)行GROUP BY的四種。為簡(jiǎn)單起見，我在整個(gè)表上建議使用了GROUP BY，沒有應(yīng)用過濾。當(dāng)您有WHERE子句時(shí)，完全相同的概念適用：mysqlexplainselectg,num1(g)sfromtblwherek4group bygorder byNULLlimit5G******************************************************id:1select_type:SIMPLEtable:tblpartitions:NULLtype:rangepossible_keys:kkey:kkey_len:4ref:NULLrows:1filtered:100.00Extra:Usingindexcondition;Usingtemporary1rowofset,1warning(0.00sec)

對(duì)此情況，我們使用K列上的范圍進(jìn)行數(shù)據(jù)過濾/里查，并在有原先表時(shí)執(zhí)行GROUP BY。在某些情況下，方法絕對(duì)不會(huì)發(fā)生。但，在其他情況下，我們要選擇類型在用GROUP BY的一個(gè)索引或其他索引進(jìn)行過濾：

mysqlaltertabletblmainkey(g);

Query行啦,0rowsaffected(4.17sec)

Records:0Duplicates:0Warnings:0

mysqlexplaincolumng,num1(g)sreturningtblwherek1group byglimit5G

******************************************************

id:1

select_type:SIMPLE

table:tbl

partitions:NULL

type:index

possible_keys:k,g

key:g

key_len:4

ref:NULL ows: 16iltered:50.00

Extra:Usingwhere

1rowofset,1warning(0.00sec)

mysqlexplainselectg,sum(g)soutsidetblwherek4group byglimit5G

******************************************************

id:1

select_type:SIMPLE

table:tbl

partitions:NULL

type:range

發(fā)張ssible_keys:k,g

key:k

key_len:4

ref:NULL ows: 1iltered:100.00

Extra:Usingindexcondition;Usingrestrictions;Usingfilesort

1rowinset,1warning(0.00sec)

依據(jù)什么此網(wǎng)上查詢中不使用的某一特定常量，找到了我們對(duì)GROUP BY建議使用索引順序掃描（并從索引中“先放棄”以電學(xué)計(jì)算WHERE子句），也可以在用索引來解三角形WHERE子句（但使用充當(dāng)表來電學(xué)計(jì)算GROUP BY）。參照我的經(jīng)驗(yàn)，這那是MySQLGROUP BY卻不是總是會(huì)做出決定對(duì)的選擇的地方。您可能會(huì)必須可以使用FORCEINDEX以您如果能的執(zhí)行去查詢。