我們緊接上樓，內(nèi)部?jī)?yōu)化完畢的sql是這樣的

SELECT d.user_name, c.timestr, kills FROM

(SELECT a.user_id, a.timestr, a.kills, COUNT(b.kills) cnt FROM kills AS a

JOIN kills b ON a.user_id = b.user_id

WHERE a.kills <= b.kills

GROUP BY a.user_id, a.timestr, a.kills) AS c

JOIN workteam d ON d.work_id = c.user_id WHERE cnt <=2;

由于 where 從句中的條件和 下面的這個(gè)子查詢相關(guān)聯(lián)，每進(jìn)行一個(gè)次外圍查詢就要執(zhí)行一次子查詢，效率不言自明。

SELECT a.user_id, a.timestr, a.kills, COUNT(b.kills) cnt FROM kills AS a

JOIN kills b ON a.user_id = b.user_id

WHERE a.kills <= b.kills

GROUP BY a.user_id, a.timestr, a.kills

我們繼續(xù)優(yōu)化。

思路是這樣的，JOIN兩次 kills 表即可， 第一次關(guān)聯(lián)是為了 查找信息， timestr 和 kills。

第二次關(guān)聯(lián)是為了 使用 count() 函數(shù)統(tǒng)計(jì) 比 當(dāng)前行 kills 大的 數(shù)量，供having 語(yǔ)句使用（

吐槽的各位看官，看到這里是不是發(fā)現(xiàn)和上節(jié)課老師所講的求每個(gè)人哪天打怪的數(shù)目最多的

思路如出一轍?。?。下面給出sql

SELECT c.user_name, a.timestr, a.kills ? FROM workteam c

JOIN kills a ON c.work_id = a.user_id

JOIN kills b ON a.user_id = b.user_id

WHERE a.kills <= b.kills

GROUP BY ?user_name, kills DESC, timestr

HAVING COUNT(b.kills) <= 2

細(xì)心的看管，已經(jīng)發(fā)現(xiàn)我這里的表名稱, 表字段和老師演示的例子不盡相同。

kills 表對(duì)應(yīng) user_kills , workteam 對(duì)應(yīng) users1,

work_id 字段對(duì)應(yīng) id

首先來(lái)看里邊的子查詢：

SELECT user_id, timestr, kills , (SELECT COUNT(*) FROM kills AS b WHERE? b.user_id = a.user_id AND a.kills <= b.kills) AS cnt FROM kills a GROUP BY user_id, timestr, kills;
這一句實(shí)際上里邊還有一個(gè)子查詢 , SELECT COUNT(*) FROM kills AS b WHERE? b.user_id = a.user_id AND a.kills <= b.kills, 這句話的關(guān)鍵在于， a.kills <= b.kills 。意思是查出整個(gè)表中比 當(dāng)前行的 kills（a表中） 大的 kills（b表中）的個(gè)數(shù)。而且 GROUP BY 子句是不必要的。

這句話的效率是相當(dāng)?shù)偷模驗(yàn)?每執(zhí)行一次外圍的 select ，就要執(zhí)行一次里邊的select，先用 JOIN優(yōu)化它。

SELECT a.user_id, a.timestr, a.kills, COUNT(b.kills) AS cnt FROM kills AS a?? ?
JOIN kills b ON a.user_id = b.user_id
WHERE a.kills <= b.kills
GROUP BY a.user_id, a.timestr, a.kills

這樣內(nèi)層的優(yōu)化就完成了，

那么怎么進(jìn)行外層優(yōu)化呢？這的確是一個(gè)問(wèn)題