mysql-php查询列表来构建表

I've had the same problem with sql for a while and as I sit down to write the same 'brute-force' hack I always use, I figure there MUST be a more efficient way to do what I want to do.

我有一段时间与sql有同样的问题,当我坐下来写同样的'暴力'黑客我总是使用,我认为必须有一个更有效的方式来做我想做的事情。

I have tables similar to this:

我有类似这样的表:

grades(gradeID, taskID, grade, username, date)
tasks(taskID, task...)
assignment(assignmentID, title...)
assignment_tasks(assignmentID, taskID)
assignment_students(assignmentID, username)
students(username, forename...)

the last 5 tables are pretty static, set up once and mostly left alone.

最后5个表是非常静态的,设置一次并且大部分都是独立的。

for the grades table, a new record is created every time a new grade is entered for a task.

对于成绩表,每次为任务输入新成绩时都会创建新记录。

I want to produce a summary table for an assignment, which might consist of say, 5 tasks and each student may have any number of grades for each task, with the most recent one being the one I want to display in the summary.

我想为作业生成一个摘要表,其中可能包括5个任务,每个学生可以为每个任务分配任意数量的成绩,最近的一个是我想要在摘要中显示的成绩。

What I'd normally do is query a list of students, and a list of assignment tasks, then build a giant nested for loop looping through each task for each student, querying the most recent grade for each, so assuming 30 students, 5 tasks, that's 152 queries, which has always struck me as waaay too many.

我通常做的是查询学生列表和分配任务列表,然后为每个学生构建一个巨大的嵌套for循环遍历每个任务,查询每个学生的最新成绩,假设30个学生,5个任务,这是152个查询,这总是让我觉得太多了。

I figure (hope) I have a horribly embarrassing gap in my sql knowledge and there's a much cleverer way to do it.

我想(希望)我的sql知识有一个非常令人尴尬的差距,并且有一个更聪明的方法来做到这一点。

Edit: Thanks for the answer - I'm still working on building the actual database so I can test it, but I suspect that the answer below doesn't cover the following issues:

编辑:感谢您的回答 - 我仍在努力构建实际的数据库,以便我可以测试它,但我怀疑下面的答案不包括以下问题:

if a student hasn't attempted a task/assignment, there won't be any entries for them in the grades table but they still need to show in the summary table with a default grade for each task ("u"). I think this bit makes it harder.

如果学生没有尝试任务/作业,则成绩表中将不会有任何条目,但他们仍需要在摘要表中显示每个任务的默认成绩(“u”)。我认为这有点难度。

Edit again: I have the embryo database now and it works in that I get a list of most recent grades with gaps where there's no grade. Transposing that list is now the subject of another question!

再次编辑:我现在有胚胎数据库,它的工作原理是我得到一个最近成绩的列表,其中没有等级的差距。转置该列表现在是另一个问题的主题!

1 个解决方案

#1

SELECT  tasks.*, students.*,
        (
        SELECT  grade
        FROM    grades
        WHERE   grades.task_id = tasks.task_id
                AND grades.username = students.username
        ORDER BY
                date DESC
        LIMIT 1
        ) AS lastgrade
FROM    assignments a
JOIN    assignment_tasks at
ON      at.assignmentID = a.assignmentID
JOIN    assignment_students ast
ON      ast.assignmentID = a.assignmentID
JOIN    tasks
ON      tasks.task_id = at.task_id
JOIN    students
ON      students.username = ast.username