排行榜 mysql 表设计_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='下一站';