英文:
PHP SQL Query to separate multiple columns (in row) into separate rows
问题
我有一个列出学生测试成绩的表格。每一行包含一个学生ID的列，有16列，每列代表一个单独的测试分数。现在我需要创建一个查询，允许我看到每个测试分数的学生ID分开的行。我在Stack Overflow上搜索了最佳方法，但没有找到看起来有效的方法。我想出了下面列出的代码，用于一个学生（1000002），但似乎对于我想要实现的目标来说过于复杂，而且只适用于三个测试分数，所以我需要添加更多内容来获得全部16个分数。是否有一种通过单个查询（首选）轻松实现这一点的方法，还是我需要提取行，然后使用循环从每个元素构建数组（较不首选）？
SELECT
scores_uid
, score1
FROM
assessment_scores WHERE fk_assigned_uid = '1000002'
UNION ALL
SELECT
scores_uid
, score2
FROM
assessment_scores WHERE fk_assigned_uid = '1000002'
UNION ALL
SELECT
scores_uid
, score3
FROM
assessment_scores WHERE fk_assigned_uid = '1000002';
英文:
I have a table listing test results for students. Each row contains a student id in one column and there are 16 columns each representing a separate test score. I need to now create a query that allows me to see the student ID with a separate row for each test score. I have searched Stack Overflow for the best way to do this but I am not finding anything that looks efficient. I came up with the code listed below for one student (1000002) but it seems overly complicated for what I'm trying to accomplish plus it's only for three of the test scores so I would have to add much more to this to get all 16. Is there an easy way to do this through a single query (prefered) or do I need to pull the row and then use a loop to build an array from each element (less prefered)?
SELECT
scores_uid
, score1
FROM
assessment_scores WHERE fk_assigned_uid = '1000002'
UNION ALL
SELECT
scores_uid
, score2
FROM
assessment_scores WHERE fk_assigned_uid = '1000002'
UNION ALL
SELECT
scores_uid
, score3
FROM
assessment_scores WHERE fk_assigned_uid = '1000002';
答案1
得分: 2
理想情况下，你会有类似这样的结构：
students（学生）
id（学生ID）name（姓名）
tests（考试）
id（考试ID）number（考试编号）
test_results（考试结果）
id（结果ID）student_id（学生ID）test_id（考试ID）result（成绩）
这样，你可以在一次查询中检索一个学生的所有成绩。
SELECT test_results.result AS 成绩,
students.name as 学生姓名,
tests.number as 考试编号
FROM test_results
LEFT JOIN students ON students.id = test_results.student_id
LEFT JOIN tests ON tests.id = test_results.test_id
WHERE student_id = {{id}}
请注意，我只提供了代码的翻译部分，没有包含其他内容。
英文:
Ideally you'd have something like this
students
idname
tests
idnumber
test_results
idstudent_idtest_idresult
This way you can query on all results of a student in one query.
SELECT test_results.result AS Result,
students.name as Student,
tests.number as Testnumber
FROM test_results
LEFT JOIN students ON students.id = test_results.student_id
LEFT JOIN tests ON tests.id = test_results.test_id
WHERE student_id = {{id}}
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论