查詢游戲歷史成績最高分前100
Sql代碼
SELECT ps.* FROM cdb_playsgame ps WHERE ps.credits=(select MAX(credits)
FROM cdb_playsgame ps1
where ps.uid=ps1.uid AND ps.gametag=ps1.gametag) AND ps.gametag='yeti3'
GROUP BY ps.uid order by ps.credits desc LIMIT 100;
Sql代碼
SELECT ps.*
FROM cdb_playsgame ps,(select ps1.uid, ps1.gametag, MAX(credits) as credits
FROM cdb_playsgame ps1 group by uid,gametag) t
WHERE ps.credits=t.credits AND ps.uid=t.uid AND ps.gametag=t.gametag AND ps.gametag='yeti3'
GROUP BY ps.uid order by ps.credits desc LIMIT 100;
執行時間僅為0.22秒，比原來的25秒提高了10000倍
查詢當天游戲最好成績
Sql代碼
SELECT ps. * , mf. * , m.username
FROM cdb_playsgame ps
LEFT JOIN cdb_memberfields mf ON mf.uid = ps.uid
LEFT JOIN cdb_members m ON m.uid = ps.uid
WHERE ps.gametag = 'chuansj'
AND FROM_UNIXTIME( ps.dateline, '%Y%m%d' ) = '20081008'
AND ps.credits = (
SELECT MAX( ps1.credits )
FROM cdb_playsgame ps1
WHERE ps.uid = ps1.uid
AND ps1.gametag = 'chuansj'
AND FROM_UNIXTIME( ps1.dateline, '%Y%m%d' ) = '20081008' )
GROUP BY ps.uid
ORDER BY credits DESC
LIMIT 0 , 50
像查詢里：
AND ps.credits=(SELECT MAX(ps1.credits)
FROM {$tablepre}playsgame ps1 where ps.uid=ps1.uid AND ps1.gametag = '$game'
AND FROM_UNIXTIME(ps1.dateline, '%Y%m%d') = '$todaytime' )
特別消耗時間
另外，像：
FROM_UNIXTIME(ps1.dateline, '%Y%m%d') = '$todaytime'
這樣的語句會導致索引無效，因為對每個dataline的值都需要用函數計算一遍，需要調整為：
Sql代碼
AND ps1.dateline >= UNIX_TIMESTAMP('$todaytime')
//更改后
Sql代碼
SELECT ps. * , mf. * , m.username
FROM cdb_playsgame ps, cdb_memberfields mf, cdb_members m, (
SELECT ps1.uid, MAX( ps1.credits ) AS credits
FROM cdb_playsgame ps1
WHERE ps1.gametag = 'chuansj'
AND ps1.dateline >= UNIX_TIMESTAMP( '20081008' )
GROUP BY ps1.uid
) AS t
WHERE mf.uid = ps.uid
AND m.uid = ps.uid
AND ps.gametag = 'chuansj'
AND ps.credits = t.credits
AND ps.uid = t.uid
GROUP BY ps.uid
ORDER BY credits DESC
LIMIT 0 , 50
對于每個球員，找出球員號碼，名字以及他所引起的罰款的號碼，但只是針對那些至少有兩次罰款的球員。
更緊湊的查詢，在FROM子句中放置一個子查詢。
Sql代碼
SELECT PLAYERNO,NAME,NUMBER
FROM (SELECT PLAYERNO,NAME,
(SELECT COUNT(*)
FROM PENALTIES
WHERE PENALTIES.PLAYERNO =
PLAYERS.PLAYERNO)
AS NUMBER
FROM PLYERS) AS PN
WHERE NUMBER>=2
FROM子句中的子查詢決定了每個球員的號碼，名字和罰款的編號。接下來，這個號碼變成了中間結果中的一列。然后指定了一個條件（NUMBER>=2）;最后，獲取SELECT子句中的列。
總結
以上就是本文關于MYSQL子查詢和嵌套查詢優化實例解析的全部內容，希望對大家有所幫助。感興趣的朋友可以參閱：mysql in語句子查詢效率慢的優化技巧示例、淺談mysql的子查詢聯合與in的效率等，如有不足之處請留言，小編會及時更正。
感謝朋友們對腳本之家網站的支持！
您可能感興趣的文章:- MySQL里面的子查詢實例
- 解決MySQL中IN子查詢會導致無法使用索引問題
- 詳細講述MySQL中的子查詢操作
- 詳解MySQL子查詢（嵌套查詢）、聯結表、組合查詢
- mysql in語句子查詢效率慢的優化技巧示例
- MySQL優化之使用連接(join)代替子查詢
- Mysql子查詢IN中使用LIMIT應用示例
- mysql實現多表關聯統計(子查詢統計)示例
- MySQL筆記之子查詢使用介紹
- MySQL子查詢中order by不生效問題的解決方法
