mysql 排序並增加序號，分數相同並列

標籤：rownum   sele   序號   SQ   FN   相同   分數   where   info   

1，排序，根據總分 增加序號

  表結構：

 

sql：

SET @row=0;
SELECT b.*, @row:[email protected]+1 rownum FROM (
select *,SUM(a.achievement) as achievementAll from cp_cj as a
where 1=1 GROUP BY a.studentNum order by SUM(a.achievement) DESC limit 0,30
) as b

結果：

 

2，排序，根據總分 增加序號，並且按照總分相同的並列

sql：

select c.* ,ifnull((
select count(*) from
(
select *,SUM(a.achievement) as achievementAll from cp_cj as a
where 1=1 GROUP BY a.studentNum order by SUM(a.achievement) DESC 
) as b
where c.achievementAll < b.achievementAll

),0)+1 as rownum from
(
select *,SUM(a.achievement) as achievementAll from cp_cj as a
where 1=1 GROUP BY a.studentNum order by SUM(a.achievement) DESC limit 0,30
) as c

結果：

 

mysql 排序並增加序號，分數相同並列