获取学生和成绩的查询SQL

Question

我有一个学生表学生(LRN，名字，名字，级别ID)和一个成绩表成绩(科目ID，年级，级别ID，LRN，时间添加)我想显示所有学生的所有科目的成绩在网上。我厌倦了这个查询，但我得到了这个结果。

Query: SELECT student.LRN, student.fName, student.lName, 
(SELECT grade.grade WHERE grade.subjectID = 'k2arts') AS 'k2arts',
(SELECT grade.grade WHERE grade.subjectID = 'k2math') AS 'k2math',
(SELECT grade.grade WHERE grade.subjectID = 'k2eng') AS 'k2eng',
(SELECT grade.grade WHERE grade.subjectID = 'k2valed') AS 'k2valed',
(SELECT grade.grade WHERE grade.subjectID = 'k2fil') AS 'k2fil' 
from student RIGHT JOIN grade on grade.LRN=student.LRN 

结果:查询结果

Answer 1

当

SELECT student.LRN, student.fName, student.lName, 
  case when grade.subjectID = 'k2arts' then grade.grade else null end AS 'k2arts',
  case when grade.subjectID = 'k2math' then grade.grade else null end AS 'k2math',
  case when grade.subjectID = 'k2eng' then grade.grade else null end AS 'k2eng',
  case when grade.subjectID = 'k2valed' then grade.grade else null end AS 'k2valed',
  case when grade.subjectID = 'k2fil' then grade.grade else null end AS 'k2fil'
from student 
LEFT JOIN grade on grade.LRN=student.LRN 

并最终使用假聚合函数删除null

SELECT student.LRN, student.fName, student.lName, 
  min(case when grade.subjectID = 'k2arts' then grade.grade else null end) AS 'k2arts',
  min(case when grade.subjectID = 'k2math' then grade.grade else null end) AS 'k2math',
  min(case when grade.subjectID = 'k2eng' then grade.grade else null end) AS 'k2eng',
  min(case when grade.subjectID = 'k2valed' then grade.grade else null end) AS 'k2valed',
  min(case when grade.subjectID = 'k2fil' then grade.grade else null end) AS 'k2fil'
from student  student.LRN, student.fName, student.lName
LEFT JOIN grade on grade.LRN=student.LRN 
group by 

Answer 2

尝试：

SELECT student.LRN, student.fName, student.lName, 
CASE WHEN grade.subjectID = 'k2arts' THEN grade.grade END AS 'k2arts',
CASE WHEN grade.subjectID = 'k2math'  THEN grade.grade END AS 'k2math',
CASE WHEN grade.subjectID = 'k2eng'   THEN grade.grade END AS 'k2eng',
CASE WHEN grade.subjectID = 'k2valed' THEN grade.grade END AS 'k2valed',
CASE WHEN grade.subjectID = 'k2fil'   THEN grade.grade END AS 'k2fil' 
from student 
LEFT JOIN grade 
on grade.LRN=student.LRN ;

Answer 3

您希望在每个子查询中建立学生的关系。您不需要在主查询中加入 Grade，因为除了通过子查询之外，您没有使用其任何值，在这种情况下，子查询位于不相关的上下文中：

SELECT student.LRN, student.fName, student.lName, 
(SELECT grade.grade FROM grade WHERE grade.subjectID = 'k2arts' AND grade.LRN=st.LRN) AS 'k2arts',
(SELECT grade.grade FROM grade WHERE grade.subjectID = 'k2math' AND grade.LRN=st.LRN) AS 'k2math',
(SELECT grade.grade FROM grade WHERE grade.subjectID = 'k2eng' AND grade.LRN=st.LRN) AS 'k2eng',
(SELECT grade.grade FROM grade WHERE grade.subjectID = 'k2valed' AND grade.LRN=st.LRN) AS 'k2valed',
(SELECT grade.grade FROM grade WHERE grade.subjectID = 'k2fil' AND grade.LRN=st.LRN) AS 'k2fil' 
from student st