**结束。**此问题需要详细的调试信息。它目前不接受答案。
**想改进这个问题吗?**更新问题,使其成为堆栈溢出的主题。
两年前关门了。
改进这个问题
与学生/年级等一起做一个项目,我需要每隔一段时间更新前三名学生。我提出了下面的问题。但是,我很难得到他们的等级/顺序。我知道如何在一个简单的查询中做到这一点,但在一个更复杂的查询中,它不起作用。我正确地获取了所有其他列,并且,对于我尝试获取order by的所有方法,我有时得到0(比如代码的当前状态),有时值是错误的(1、11、10),等等。
注意:我已经检查了各种问题(包括下面的问题),但是我不知道如何将它们放入我的查询中。
在mysql中生成排名的最佳方法是什么?
总结:
目标:
-从中得到每个学生的分数总和 marks
,将其除以表中的条目数(再次 marks
). 学生来自某一年级。
-使用 sum(mark)
对这些学生进行排名。
-获得前三名。
-把那个年级的前三名学生排在第一位 TopStudents
表,以及他们的平均分数(如 sum
)还有他们的身份证。
table:
学生表包含有关学生的信息,包括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 | |
+-------------+---------------------+------+-----+---------+----------------+
班级表每个年级的班级。参考资料表
+-------------+---------------------+------+-----+---------+----------------+
| 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表。
+-------------+---------------------+------+-----+---------+----------------+
| 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
1条答案
按热度按时间g9icjywg1#
在较新版本的mysql中,在分配列组之前,需要使用派生表进行排序:
我几乎可以肯定
sum
是不正确的,你真的打算avg(mark)
. 然而,这就是你的问题的逻辑。