mysql 查排名

標籤：

  SET @amount=0;
  SET @rank=1;
  SET @shunxu=0;
 SELECT tmp2.id AS id,tmp2.name AS NAME,tmp2.amount AS amount,tmp2.rank  AS rank
 FROM (
 SELECT tmp.id AS id,tmp.name AS NAME,tmp.amount AS amount,@shunxu:[email protected]+1,IF([email protected],@rank,@rank:[email protected]) AS rank,@amount:=amount
 FROM(
 SELECT  `ti`.`user_id` AS `id`, 
 (SELECT  `tu`.`name`  FROM `t_users` `tu`  WHERE (`tu`.`id` = `ti`.`user_id`)) AS `name`, 
 SUM(`ti`.`amount`) AS `amount` FROM `t_invests` `ti`
 GROUP BY `ti`.`user_id`
 ORDER BY SUM(`ti`.`amount`)DESC) tmp)tmp2

mysql 查排名