一路随行

查询游戏历史成绩最高分前100

Sql代码

1. SELECT ps.* FROM cdb_playsgame ps WHERE ps.credits=(select MAX(credits)    
2.  FROM cdb_playsgame ps1    
3. where ps.uid=ps1.uid AND ps.gametag=ps1.gametag) AND ps.gametag=‘yeti3′    
4. GROUP BY ps.uid order by ps.credits desc LIMIT 100;  

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代码

1. SELECT ps.*    
2. FROM cdb_playsgame ps,(select ps1.uid, ps1.gametag, MAX(credits) as credits   
3. FROM cdb_playsgame ps1 group by uid,gametag) t   
4. WHERE ps.credits=t.credits AND ps.uid=t.uid AND ps.gametag=t.gametag AND ps.gametag=‘yeti3′    
5. GROUP BY ps.uid order by ps.credits desc LIMIT 100;  

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代码

1.  SELECT ps. * , mf. * , m.username   
2. FROM cdb_playsgame ps   
3. LEFT JOIN cdb_memberfields mf ON mf.uid = ps.uid   
4. LEFT JOIN cdb_members m ON m.uid = ps.uid   
5. WHERE ps.gametag = ‘chuansj’  
6. AND FROM_UNIXTIME( ps.dateline, ‘%Y%m%d’ ) = ’20081008′  
7. AND ps.credits = (   
8. SELECT MAX( ps1.credits )   
9. FROM cdb_playsgame ps1   
10. WHERE ps.uid = ps1.uid   
11. AND ps1.gametag = ‘chuansj’  
12. AND FROM_UNIXTIME( ps1.dateline, ‘%Y%m%d’ ) = ’20081008′ )   
13. GROUP BY ps.uid   
14. ORDER BY credits DESC  
15. LIMIT 0 , 50   

 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

像查询里

Sql代码

1. AND ps.credits=(SELECT MAX(ps1.credits)    
2.   FROM {$tablepre}playsgame ps1 where ps.uid=ps1.uid AND ps1.gametag = ‘$game’     
3.   AND FROM_UNIXTIME(ps1.dateline, ‘%Y%m%d’) = ‘$todaytime’  )  

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'  )

特别消耗时间

另外，像：

Sql代码

1. FROM_UNIXTIME(ps1.dateline, ‘%Y%m%d’) = ‘$todaytime’  

FROM_UNIXTIME(ps1.dateline, '%Y%m%d') = '$todaytime'

这样的语句会导致索引无效，因为对每个dataline的值都需要用函数计算一遍，需要调整为：

Sql代码

1. AND ps1.dateline >= UNIX_TIMESTAMP(‘$todaytime’)    

AND ps1.dateline >= UNIX_TIMESTAMP('$todaytime')

//更改后

Sql代码

1.  SELECT ps. * , mf. * , m.username   
2. FROM cdb_playsgame ps, cdb_memberfields mf, cdb_members m, (   
3.   
4. SELECT ps1.uid, MAX( ps1.credits ) AS credits   
5. FROM cdb_playsgame ps1   
6. WHERE ps1.gametag = ‘chuansj’  
7. AND ps1.dateline >= UNIX_TIMESTAMP( ’20081008′ )   
8. GROUP BY ps1.uid   
9. ) AS t   
10. WHERE mf.uid = ps.uid   
11. AND m.uid = ps.uid   
12. AND ps.gametag = ‘chuansj’  
13. AND ps.credits = t.credits   
14. AND ps.uid = t.uid   
15. GROUP BY ps.uid   
16. ORDER BY credits DESC  
17. LIMIT 0 , 50   

 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代码

1. SELECT PLAYERNO,NAME,NUMBER   
2. FROM (SELECT PLAYERNO,NAME,   
3.              (SELECT COUNT(*)   
4.               FROM PENALTIES   
5.               WHERE PENALTIES.PLAYERNO =   
6.                     PLAYERS.PLAYERNO)   
7.               AS NUMBER   
8.        FROM PLYERS) AS PN   
9. WHERE NUMBER>=2  

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子句中的列。

转自：http://dodomail.javaeye.com/blog/250199