First initialize a tableDROP TABLE IF EXISTS' lq_wx_sign ';CREATE TABLE' lq_wx_sign ' (' ID ')int(2) unsigned not NULLAuto_increment COMMENT'primary Key ID', ' name 'varchar( -) not NULLCOMMENT'User name', ' open_id 'varchar( -) not NULL DEFAULT "'COMMENT'User open_id', ' Img_url 'varchar(1024x768) not NULL DEFAULT "'COMMENT'Avatar Address', ' addr 'varchar(1024x768) not NULL DEFAULT "'COMMENT'Address', ' sex 'varchar(8) not NULL DEFAULT "'COMMENT'Sex', ' Sign_time 'datetime not NULLCOMMENT'Registration Date', ' Points 'int( One) not NULL DEFAULT '0'COMMENT'points obtained by sign-in',PRIMARY KEY(' id '),KEY' open_id ' (' open_id ')) ENGINE=InnoDBDEFAULTCHARSET=UTF8 COMMENT='Registration Form';INSERT into' Lq_wx_sign 'VALUES(NULL,'Next Stop','O3p94s8np6-dofti4jymwgxvjzio','http://wx.qlogo.cn/mmopen/ smzokpibpac17pucyhbxkibviahvbzxicunmoiadbscnwicnvwdxibhtzwmvj9jduwbs3iamreecxlwet8wvaibmlemy7jwlsnq1sfqys/0 ','China/Guangdong/Zhuhai','male','2015-08-04 18:14:22',' -');INSERT into' Lq_wx_sign 'VALUES('NULL,'Next stop x Wait', 'O3p94s8np6-Dofti4jymwgxvjzio', 'http//wx.qlogo.cn/Mmopen/Smzokpibpac17pucyhbxkibviahvbzxicunmoiadbscnwicnvwdxibhtzwmvj9jduwbs3iamreecxlwet8wvaibmlemy7jwlsnq1sfqys/0', 'China/Guangdong/Zhuhai', 'Man', ' -- ,- - -: the:Panax Notoginseng', ' A'INSERT into ' lq_wx_sign ' VALUES (null,'Next stop x wait @,'O3p94s8np6-dofti4jymwgxvjzio','http://wx.qlogo.cn/mmopen/ smzokpibpac17pucyhbxkibviahvbzxicunmoiadbscnwicnvwdxibhtzwmvj9jduwbs3iamreecxlwet8wvaibmlemy7jwlsnq1sfqys/0 ','China/Guangdong/Zhuhai','male','2015-08-19 09:49:58',' -');INSERT into' Lq_wx_sign 'VALUES(NULL,'Xinya','O3p94sxexy956kvdq3vorvofh2zu','http://wx.qlogo.cn/mmopen/ q3auhgzwzm7ckbianmiw335amwoyaw7tn7dn13u8ztq70a418meiw1ekn4nvcy1qtur8ekxhehg5nc91z7k0qsa/0','China/Guangdong/Zhuhai','female','2015-08-05 09:53:38',' -');INSERT into' Lq_wx_sign 'VALUES(NULL,'Xiang','O3p94sxexy956kvdq3vorvofh2zu','http://wx.qlogo.cn/mmopen/ q3auhgzwzm7ckbianmiw335amwoyaw7tn7dn13u8ztq70a418meiw1ekn4nvcy1qtur8ekxhehg5nc91z7k0qsa/0','China/Guangdong/Zhuhai','female','2015-08-04 09:54:46',' -');INSERT into' Lq_wx_sign 'VALUES(NULL,'Xyang','O3p94sxexy956kvdq3vorvofh2zu','http://wx.qlogo.cn/mmopen/ q3auhgzwzm7ckbianmiw335amwoyaw7tn7dn13u8ztq70a418meiw1ekn4nvcy1qtur8ekxhehg5nc91z7k0qsa/0','China/Guangdong/Zhuhai','female','2015-08-19 14:20:37',' the');INSERT into' Lq_wx_sign 'VALUES(NULL,'XINYG','O3p94sxexy956kvdq3vorvofh2zu','http://wx.qlogo.cn/mmopen/ q3auhgzwzm7ckbianmiw335amwoyaw7tn7dn13u8ztq70a418meiw1ekn4nvcy1qtur8ekxhehg5nc91z7k0qsa/0','China/Guangdong/Zhuhai','female','2015-08-20 14:42:45',' the'); Achieving rankings in MySQLSet @rownum1=0;Select @rownum1:=@rownum1+1 asRownum,name,points fromLq_wx_user_pointsOrder byPointsdescLIMIT0,TenTo achieve a personal query rankingSet @rownum=0; Select * from(Select @rownum:=@rownum+1 asRownum,name,points fromLq_wx_user_pointsOrder byPointsdescLIMIT0,Ten) asTpwhereName='Next Stop'; Implement a SQL query out of rankSelect * from(Select(@rownum:=@rownum+1) asRownum,name,points fromLq_wx_user_points, (SELECT @rownum:=0) AOrder byPointsdesc) asTpwhereName='Next Stop';
MySQL score table Implementation ranking