mysql-php 查询列表以建表 [英] mysql-php querying a list to build a table

问题描述

我在 sql 上遇到了同样的问题，当我坐下来编写我经常使用的相同的蛮力"hack 时，我认为必须有一种更有效的方法来做我想做的事情.

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.

我有类似这样的表格:

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

最后 5 个表是非常静态的，设置了一次并且大部分是单独放置的.

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

对于成绩表，每次为任务输入新成绩时都会创建一条新记录.

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

我想为一项作业制作一个汇总表，其中可能包含 5 个任务，每个学生的每项任务可能有任意数量的成绩，最近的一个是我想在汇总中显示的成绩.

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.

我通常做的是查询学生列表和作业任务列表，然后构建一个巨大的嵌套 for 循环遍历每个学生的每个任务，查询每个学生的最新成绩，因此假设有 30 个学生, 5 个任务，也就是 152 个查询，这让我觉得太多了.

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.

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

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

感谢您的回答 - 我仍在构建实际的数据库，以便我可以对其进行测试，但我怀疑下面的回答没有涵盖以下问题:

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:

如果学生没有尝试过任务/作业，成绩表中不会有任何条目，但他们仍然需要在汇总表中显示每个任务的默认成绩(u").我认为这会让它变得更难.

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.

再次我现在有胚胎数据库，它的工作原理是我得到一个最近成绩的列表，其中有没有成绩的差距.转换该列表现在是另一个问题的主题！

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!

推荐答案

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

这篇关于mysql-php 查询列表以建表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！