MYSQL的一些技巧

给查询结果增加伪列(感觉不够稳定)：

set @i=0; select * from (select @i:=@i+1 as rank, id from player_info) as rank_player_info

给查询结果增加伪列(感觉稳定)：

select gold, (@i:=@i+1)as rank from player_info ,(select @i:=0) as rank_table

查询rank：

select id,gold,(select count(*) from player_info where gold>= (select gold
from player_info where id = 100022 order by gold desc limit 1)) as rank from player_info where id = 100022;

大于某个值的若干记录

select id, gold from player_info where gold>= (select gold
from player_info where id = 100013) order by gold desc limit 9

排行榜中位于某个值前面若干位的若干记录

select * from (select id, gold from player_info where gold>= (select gold
    from player_info where id = 100013) order by gold limit 9) as t order by gold desc

排行榜中位于某个值前面若干位的若干记录，附加rank信息

SELECT *
FROM (
SELECT gold, id, (
@i := @i -1
) AS rank
FROM (
SELECT id, gold
FROM player_info
WHERE gold >= (
SELECT gold
FROM player_info
WHERE id =100013 )
LIMIT 10
) AS a, (
SELECT @i := (
SELECT count( * )
FROM player_info
WHERE gold >= (
SELECT gold
FROM player_info
WHERE id =100013 ) ) AS base
) AS b
) AS c
ORDER BY rank

排行榜TOP10

select id,gold,(@i:=@i  1)as rank from (select id, gold from player_info order by gold desc limit 10)as a ,(select @i:=0) as rank_table

更新rank字段

UPDATE player_info a,
(
 SELECT magic_code,gold,gold_rank, @var:=@var+1 AS PLACE
 FROM player_info,(select @var:=0) as rank_table
 ORDER BY gold desc
) b
SET  a.gold_rank=b.PLACE
WHERE a.magic_code = b.magic_code;