mysql子查询多重嵌套实例_MYSQL子查询和嵌套查询优化实例解析

查询游戏历史成绩最高分前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.gam