按分组排序, 并查出每个分组的前 3 名
- SELECT
- *
- FROM
- (
- SELECT
- ZONEID,
- uid,
- NAME,
- fight,
- IF
- ( @zone = zoneid, @rank := @rank + 1, @rank := 1 ) AS rank,
- ( @zone := zoneid ) AS zone
- FROM
- `user` u,
- ( SELECT @rank := 0 ) t,
- ( SELECT @zone := 1 ) z
- ORDER BY
- zoneid DESC,
- fight DESC
- ) a
- WHERE
- rank <= 3
- ORDER BY
- zoneid DESC,
- rank DESC
来源: http://www.bubuko.com/infodetail-3257962.html