phpmyadmin 查询将相同的等级分配给所有用户

vmdwslir 于 2022-12-13 发布在 PHP

关注(0)|答案(1)|浏览(91)

我想根据学生的总成绩得到他们在班级中的排名/位置。
但是，我的查询一直向每个学生输出相同的排名;使它们都处于第一位置。
最初，我尝试了这个查询，它运行得很好，但它会显示出一个班级中的所有学生。

SELECT `student_id`, rank, totscore
FROM (SELECT *, IF(@marks=(@marks:=totscore), @auto, @auto:=@auto+1) AS rank
FROM (SELECT * FROM
      (SELECT `student_id`, SUM(`ft_tot_score`) AS totscore, `class_id`, `section_id`
       FROM ftscores_primary,
       (SELECT @auto:=0, @marks:=0) as init WHERE class_id = 8 and section_id = 2 and session_id = 17
       GROUP BY `student_id`) sub ORDER BY totscore DESC)t) as result

输出如下：

student_id | rank | totscore
402        |  1   |   869
1314       |  2   |   849
1024       |  3   |   842

但我想要这样的东西：

student_id | rank | totscore
1024       |  3   |  842

这就是我所做的

SELECT `student_id`, rank, totscore
FROM (SELECT *, IF(@marks=(@marks:=totscore), @auto, @auto:=@auto+1) AS rank
FROM (SELECT * FROM
      (SELECT `student_id`, SUM(`ft_tot_score`) AS totscore, `class_id`, `section_id`
       FROM ftscores_primary,
       (SELECT @auto:=0, @marks:=0) as init WHERE class_id = 8 and section_id = 2 and session_id = 17 and student_id = 1024
       GROUP BY `student_id`) sub ORDER BY totscore DESC)t) as result

这是我得到的

student_id |  rank | totscore
1024       |   1   |  842

phpmyadmin

来源：https://stackoverflow.com/questions/74713386/query-assigns-the-same-rank-to-all-users

1条答案

按热度按时间

kxeu7u2r1#

按如下方式修改查询：

SELECT student_id, rank, totscore
FROM
(
  SELECT student_id, totscore, 
       IF(@marks=(@marks:=totscore), @auto, @auto:=@auto+1) AS rank
  FROM
  (
    SELECT student_id, SUM(ft_tot_score) AS totscore
    FROM ftscores_primary, (SELECT @auto:=0, @marks:=0) as init
    WHERE class_id = 8 and section_id = 2 and session_id = 17
    GROUP BY student_id
  ) T
  ORDER BY totscore DESC
) D
WHERE rank= @auto -- @auto holds the last rank value

请参见demo。

赞(0）回复(0）举报 2022-12-13

我来回答

phpmyadmin 查询将相同的等级分配给所有用户

1条答案

相关问题

热门标签

最新问答