代码片段

数据库根据得分情况排名
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
#根据得分情况排名
#如果得分一样,则并列名次,且下一名次依然+1
#如:10分,9分,9分,8分,排名后的名次为 1,2,2,4

SET @score_total = 0; #总分
SET @rank = 1; #排名
SET @sort = 0;

UPDATE target_table,
(
SELECT
id,
uname,
score_total,
@sort := @sort + 1,
IF
( score_total = @score_total, @rank, @rank := @sort ) AS rank,
@score_total := score_total
FROM
target_table
WHERE month={$this->month} and com_id in ({$com_ids})
ORDER BY
score_total DESC,CONVERT(uname using gbk) asc
) AS t2
SET target_table.rank = t2.rank
WHERE
target_table.id = t2.id
-------------完!感谢阅读-------------