php - Codeigniter评分排名

Question

我正在尝试根据表格中的分数来获取学生的排名。它看起来并不太复杂，但我不明白。理想情况下，该函数将采用 Stu_id(整数)并根据包含“stu_id”、“stu_nm”和“stu_scr”列的表输出其排名。

我已经查看了这里的其他问题，但我不确定我是否理解如何将答案应用于我的具体问题。我看到另一个线程尝试类似的解决方案:

SELECT s1.initials, (
  SELECT COUNT(*)
  FROM scores AS s2
  WHERE s2.score > s1.score
)+1 AS rank
FROM scores AS s1

并尝试将其应用到我修改为的代码中:

$query = $this
                ->db
                ->select('students.stu_id, (SELECT COUNT(*) FROM students AS s2 WHERE s2.run_scr > s1.run_scr)
                            +1 AS rank FROM students AS s1')
                ->get('students');

运行代码会导致此错误:

Error Number: 1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL         
server version for the right syntax to use near 'FROM (`students`)' at line 3

SELECT `students`.`stu_id`, (SELECT COUNT(*) FROM students AS s2 WHERE s2.run_scr >    
s1.run_scr) +1 AS rank FROM students AS s1 FROM (`students`)

Filename: /home2/uder/public_html/scorer/models/get_score.php

Line Number: 78    

如果您能提供有关如何解决此问题的指导或更好解决方案的建议，我将不胜感激。

Answer 1

您使用事件记录编写排名查询的方式是错误的，请尝试这种方式

$query = $this->db
              ->select('s1.stu_id,
              (SELECT COUNT(*) FROM students AS s2 WHERE s2.run_scr > s1.run_scr) +1 AS `rank`' ,
                      FALSE)
              ->get('students s1');

或

$query = $this->db
              ->select('s1.stu_id,
              (SELECT COUNT(*) FROM students AS s2 WHERE s2.run_scr > s1.run_scr) +1 AS `rank`' ,
                      FALSE)
              ->from('students s1')
              ->get();