mysql 分数表实现排名

DROP TABLE IF EXISTS `lq_wx_sign`; CREATE TABLE `lq_wx_sign` ( `id` int(2) unsigned NOT NULL AUTO_INCREMENT COMMENT ‘主键ID‘, `name` varchar(30) NOT NULL COMMENT ‘用户名‘, `open_id` varchar(64) NOT NULL DEFAULT ‘‘ COMMENT ‘用户open_id‘, `img_url` varchar(1024) NOT NULL DEFAULT ‘‘ COMMENT ‘头像地址‘, `addr` varchar(1024) NOT NULL DEFAULT ‘‘ COMMENT ‘地址‘, `sex` varchar(8) NOT NULL DEFAULT ‘‘ COMMENT ‘性别‘, `sign_time` datetime NOT NULL COMMENT ‘签到日期‘, `points` int(11) NOT NULL DEFAULT ‘0‘ COMMENT ‘签到获取的积分‘, PRIMARY KEY (`id`), KEY `open_id` (`open_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=‘微信签到表‘; INSERT INTO `lq_wx_sign` VALUES (null, ‘下一站‘, ‘o3p94s8nP6-doFTI4JYMWgXVJzio‘, ‘http://wx.qlogo.cn/mmopen/SMzokpibpAC17PUCyHbxKibViaHvbZxicUNMoiadBsCnwicNvwdXibhTzWmvJ9jdUWbs3iaMREeCxLweT8WVAibMleMy7jwlsNQ1SFQyS/0‘, ‘中国/广东/珠海‘, ‘男‘, ‘2015-08-04 18:14:22‘, ‘300‘); INSERT INTO `lq_wx_sign` VALUES (‘null, ‘下一站×等待‘, ‘o3p94s8nP6-doFTI4JYMWgXVJzio‘, ‘http://wx.qlogo.cn/mmopen/SMzokpibpAC17PUCyHbxKibViaHvbZxicUNMoiadBsCnwicNvwdXibhTzWmvJ9jdUWbs3iaMREeCxLweT8WVAibMleMy7jwlsNQ1SFQyS/0‘, ‘中国/广东/珠海‘, ‘男‘, ‘2015-08-18 18:15:37‘, ‘61‘); INSERT INTO `lq_wx_sign` VALUES (null, ‘下一站×等待@, ‘o3p94s8nP6-doFTI4JYMWgXVJzio‘, ‘http://wx.qlogo.cn/mmopen/SMzokpibpAC17PUCyHbxKibViaHvbZxicUNMoiadBsCnwicNvwdXibhTzWmvJ9jdUWbs3iaMREeCxLweT8WVAibMleMy7jwlsNQ1SFQyS/0‘, ‘中国/广东/珠海‘, ‘男‘, ‘2015-08-19 09:49:58‘, ‘38‘); INSERT INTO `lq_wx_sign` VALUES (null, ‘xinya‘, ‘o3p94sxeXY956Kvdq3voRvOfh2ZU‘, ‘http://wx.qlogo.cn/mmopen/Q3auHgzwzM7ckBianmIw335AmWOyaw7Tn7Dn13u8zTQ70a418meIW1ekN4nvCy1qtUr8EKxHehG5nC91z7k0qSA/0‘, ‘中国/广东/珠海‘, ‘女‘, ‘2015-08-05 09:53:38‘, ‘300‘); INSERT INTO `lq_wx_sign` VALUES (null, ‘xiang‘, ‘o3p94sxeXY956Kvdq3voRvOfh2ZU‘, ‘http://wx.qlogo.cn/mmopen/Q3auHgzwzM7ckBianmIw335AmWOyaw7Tn7Dn13u8zTQ70a418meIW1ekN4nvCy1qtUr8EKxHehG5nC91z7k0qSA/0‘, ‘中国/广东/珠海‘, ‘女‘, ‘2015-08-04 09:54:46‘, ‘70‘); INSERT INTO `lq_wx_sign` VALUES (null, ‘xyang‘, ‘o3p94sxeXY956Kvdq3voRvOfh2ZU‘, ‘http://wx.qlogo.cn/mmopen/Q3auHgzwzM7ckBianmIw335AmWOyaw7Tn7Dn13u8zTQ70a418meIW1ekN4nvCy1qtUr8EKxHehG5nC91z7k0qSA/0‘, ‘中国/广东/珠海‘, ‘女‘, ‘2015-08-19 14:20:37‘, ‘49‘); INSERT INTO `lq_wx_sign` VALUES (null, ‘xinyg‘, ‘o3p94sxeXY956Kvdq3voRvOfh2ZU‘, ‘http://wx.qlogo.cn/mmopen/Q3auHgzwzM7ckBianmIw335AmWOyaw7Tn7Dn13u8zTQ70a418meIW1ekN4nvCy1qtUr8EKxHehG5nC91z7k0qSA/0‘, ‘中国/广东/珠海‘, ‘女‘, ‘2015-08-20 14:42:45‘, ‘39‘); 在mysql中实现排名 set @rownum1=0; select @rownum1:=@rownum1+1 as rownum,name,points from lq_wx_user_points order by points desc LIMIT 0,10; 实现个人查询排名 set @rownum=0; select * from (select @rownum:=@rownum+1 as rownum,name,points from lq_wx_user_points order by points desc LIMIT 0,10) as tp where name=‘下一站‘; 实现一条sql查询出排名 select * from ( select (@rownum:=@rownum+1) as rownum,name,points from lq_wx_user_points ,( SELECT @rownum:=0) a order by points desc )as tp where name=‘下一站‘;

 

mysql 分数表实现排名

标签：