查詢游戲歷史成績最高分前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子查詢和嵌套查詢優化實例解析的全部內容，希望對大家有所幫助。如有不足之處請留言，小編會及時更正。感謝朋友們對VeVb武林網網站的支持！
新聞熱點
疑難解答
