查詢游戲歷史成績最高分前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子句中的列。
新聞熱點
疑難解答
