查询游戏历史成绩最高分前100
Sql代码
1
2
3
4
|
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
2
3
4
5
|
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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
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
|
像查询里:
1
2
3
|
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'
)
|
特别消耗时间
另外,像:
1
|
FROM_UNIXTIME(ps1.dateline,
'%Y%m%d'
) =
'$todaytime'
|
这样的语句会导致索引无效,因为对每个dataline的值都需要用函数计算一遍,需要调整为:
Sql代码
1
|
AND
ps1.dateline >= UNIX_TIMESTAMP(
'$todaytime'
)
|
//更改后
Sql代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
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
2
3
4
5
6
7
8
9
|
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的效率等,如有不足之处请留言,小编会及时更正