与学生/成绩/等一起做一个项目，我需要每隔一段时间更新前 3 名学生。我想出了下面的查询。但是，我无法获得他们的排名/顺序。我知道如何在一个简单的查询中做到这一点，但在一个更复杂的查询中，它不起作用。我正确获取了所有其他列，并且使用我尝试获取顺序的所有方法，有时我会得到 0（例如代码的当前状态），有时会得到错误的值（1、11、10 ）， 等等。
注意：我检查了各种问题（包括下面的问题），但我不知道如何将它们放在我的查询中。
总结：
目标：
- 从 中获得每个学生的分数总和marks，除以表格中的条目数（再次marks）。学生来自给定的年级。
- 用于sum(mark)对这些学生进行排名。
- 获得前三名。
- 将那个年级的前三名学生TopStudents，以及他们的平均分数（as sum）和他们的 id放在表中。
表格：
学生表包含有关学生的信息，包括 id：
+-------------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------------+------+-----+---------+----------------+
| id | int (20) unsigned | NO | PRI | NULL | auto_increment |
| name |varchar(20) unsigned | NO | | NULL | |
+-------------+---------------------+------+-----+---------+----------------+
分数表包含每个学生在每次考试中的分数
+-------------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------------+------+-----+---------+----------------+
| id |int (20) unsigned | NO | PRI | NULL | auto_increment |
| idStudent |int (20) unsigned | NO | FOR | NULL | |
| mark |tinyInt (3) unsigned | NO | | NULL | |
| idExam |int (20) unsigned | NO | FOR | NULL | |
+-------------+---------------------+------+-----+---------+----------------+
成绩表具有成绩 ID 和名称：
+-------------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------------+------+-----+---------+----------------+
| id | int (20) unsigned | NO | PRI | NULL | auto_increment |
| name |varchar(20) unsigned | NO | | NULL | |
+-------------+---------------------+------+-----+---------+----------------+
Class每个年级的表类。参考表
+-------------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------------+------+-----+---------+----------------+
| id | int (20) unsigned | NO | PRI | NULL | auto_increment |
| name |varchar(20) unsigned | NO | | NULL | |
| idGrade | int (20) unsigned | NO | FOR | NULL | |
+-------------+---------------------+------+-----+---------+----------------+
最后，臭名昭著的TopStudents Table。
+-------------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------------+------+-----+---------+----------------+
| id | int (20) unsigned | NO | PRI | NULL | auto_increment |
| idStudent | int (20) unsigned | NO | FOR | NULL | |
| sumMarks | int (20) unsigned | NO | | NULL | |
| rank |tinyInt (1) unsigned | NO | | NULL | |
| date |date unsigned | NO | | NULL | |
+-------------+---------------------+------+-----+---------+----------------+
尝试：尝试 1：错误：所有等级均为 0
INSERT INTO topStudents(`date`, idStudent, `sum`, `order`)
SELECT
'2018-10-10' AS DATE,
student.id AS idStudent,
AVG(marks.mark)
@n = @n + 1 AS `order`
FROM
marks
INNER JOIN student ON student.id = marks.idStudent
INNER JOIN class ON class.id = marks.idClass
INNER JOIN grade ON class.idGrade = grade.id
WHERE
grade.id = 2
GROUP BY
marks.idStudent
ORDER BY
SUM(mark)
DESC
LIMIT 3
尝试 2：排名返回：1、11、10
SET @n := 0;
INSERT INTO topStudents(`date`, idStudent, `sum`, `rank`)
SELECT
'2018-10-10' AS DATE,
tbl.idStudent AS idStudent,
AVG(tbl.mark) AS `sum`,
rnk AS `rank`
FROM (SELECT student.id AS idStudent, SUM(mark) AS mark FROM
marks
INNER JOIN student ON student.id = marks.idStudent
INNER JOIN class ON class.id = marks.idClass
INNER JOIN grade ON class.idGrade = grade.id
WHERE
grade.id = 2
GROUP BY
marks.idStudent
ORDER BY
SUM(mark)
DESC
LIMIT 3) AS tbl, (SELECT @n = @n + 1) AS rnk
在更新的 MySQL 版本中，您需要在分配排名之前使用派生表进行排序：
INSERT INTO topStudents (`date`, idStudent, `sum`, `order`)
SELECT date, idStudent, `sum`, (@n := @n + 1) AS `order`
FROM (SELECT '2018-10-10' AS DATE, s.id AS idStudent,
SUM(m.mark) / (SELECT COUNT(*) FROM marks m2 WHERE m2.idStudent = m.idStudent) AS `sum`
FROM marks m JOIN
student s
ON s.id = m.idStudent JOIN
class c
ON c.id = m.idClass JOIN
grade g
ON c.idGrade = g.id
WHERE g.id = 2
GROUP BY m.idStudent
ORDER BY SUM(mark) DESC
LIMIT 3
) sm CROSS JOIN
(SELECT @n := 0) params;
我几乎可以肯定 的计算sum是不正确的，并且您确实打算avg(mark). 但是，这是您在问题中的逻辑。
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句