用户积分排行求前100名,名次是并列的。
即:相同积分的用户并列名次,如何查询?
即:相同积分的用户并列名次,如何查询?
解决方案 »
- Mysql 排序问题
- 大家一般都用什么工具debug Mysql的函数procedure
- 把vfp中的表导入到mysql
- linux下如何在另一个端口安装高版本mysql
- 请教,为什么我只能以localhost方式连接mysql?
- mysql 能否在存储过程中创建和读取文件
- mysql支持中文表名的问题
- mysql数据表优化问题
- MySQL多主键,其中一个主键使用AUTO_INCREMENT自增,结果报错!!
- 命令行登录数据库报2013错误
- Show Variables Like 'BaseDir%' 求BaseDir的地址值
- Show Variables Like 'BaseDir%' 求BaseDir的地址值
思路:
先将所有分数排名,再与工作表连接
+------+-------+
| name | score |
+------+-------+
| A | 80 |
| B | 90 |
| C | 99 |
| D | 95 |
| E | 75 |
| F | 90 |
| G | 80 |
+------+-------+
7 rows in set (0.00 sec)mysql> SET @b=0,@c=0;
Query OK, 0 rows affected (0.00 sec)mysql> SELECT NAME,score,rank FROM (
-> SELECT NAME,@a:=score AS score,@b:=IF(@c<>@a,@b+1,@b) AS rank,@c:=@a AS d
ummy
-> FROM tab ORDER BY score DESC
-> ) a;
+------+-------+------+
| NAME | score | rank |
+------+-------+------+
| C | 99 | 1 |
| D | 95 | 2 |
| B | 90 | 3 |
| F | 90 | 3 |
| A | 80 | 4 |
| G | 80 | 4 |
| E | 75 | 5 |
+------+-------+------+
7 rows in set (0.00 sec)
-- Table structure for `tb_user`
-- ----------------------------
DROP TABLE IF EXISTS `tb_user`;
CREATE TABLE `tb_user` (
`id` int(11) unsigned NOT NULL auto_increment,
`name` varchar(50) default NULL,
`score` int(11) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;-- ----------------------------
-- Records of tb_user
-- ----------------------------
INSERT INTO `tb_user` VALUES ('1', 'aaa', '150');
INSERT INTO `tb_user` VALUES ('2', 'bbb', '150');
INSERT INTO `tb_user` VALUES ('3', 'ccc', '120');
INSERT INTO `tb_user` VALUES ('4', 'ddd', '100');
INSERT INTO `tb_user` VALUES ('5', 'eee', '90');
INSERT INTO `tb_user` VALUES ('6', 'fff', '90');
INSERT INTO `tb_user` VALUES ('7', 'ggg', '80');
INSERT INTO `tb_user` VALUES ('8', 'hhh', '70');
INSERT INTO `tb_user` VALUES ('9', 'iii', '60');
INSERT INTO `tb_user` VALUES ('10', 'jjj', '50');
INSERT INTO `tb_user` VALUES ('11', 'kkk', '40');
INSERT INTO `tb_user` VALUES ('12', 'lll', '30');
--现在按score列排序,查询前10名-- -----------------------------------
--名次 用户 分数
-- 1 aaa 150
-- 1 bbb 150
-- 2 ccc 120
-- 3 ddd 100
-- 4 eee 90
-- 4 fff 90
-- 5 ggg 80
-- 6 hhh 70
-- 7 iii 60
-- 8 jjj 50
-- 9 kkk 40
-- 10 lll 30
-- 要求:1.分数相同的并列名次
-- 2.考虑可能用户数量少,排名排不到第10名
SELECT rank,NAME,score FROM (
SELECT NAME,@a:=score AS score,@b:=IF(@c<>@a,@b+1,@b) AS rank,@c:=@a AS dummy
FROM tb_user ORDER BY score DESC
) a WHERE rank <=10;
SELECT a.*,a2.pm FROM tb_user a INNER JOIN (SELECT *,@num:=@num+1 AS pm FROM (
SELECT score FROM `tb_user` GROUP BY score DESC) a1) a2
ON a2.score=a.score WHERE pm<=10;
INNER JOIN (
SELECT a.score,COUNT(DISTINCT b.score) pm FROM tb_user a INNER JOIN tb_user b ON a.score<=b.score
GROUP BY a.score) c ON a.score=c.score WHERE pm<=10