带有联接的SQL查询重复所有其他行的最顶层数据行

The following SQL query retrieves and repeats the data of the top most row for all the other rows.

以下SQL查询检索并重复所有其他行的最顶行的数据。

SELECT 
    Student.StuRollNo, Student.StuName, Student.StuFName, 
    (SELECT TOP 1 M.SubjectObtMarks 
     FROM Marking M 
     WHERE M.SubjectID = 'SubUrdu' 
       AND M.ExamID = 1) AS Urdu,
    (SELECT TOP 1 M.SubjectObtMarks 
     FROM Marking M 
     WHERE M.SubjectID = 'SubEng' 
       AND M.ExamID = 1) AS Eng,
    (SELECT TOP 1 M.SubjectObtMarks 
     FROM Marking M 
     WHERE M.SubjectID = 'SubPhy' 
       AND M.ExamID = 1) AS Phy,
    (SELECT TOP 1 M.SubjectObtMarks 
     FROM Marking M 
     WHERE M.SubjectID = 'SubChem' 
       AND M.ExamID = 1) AS Chem,
    (SELECT TOP 1 M.SubjectObtMarks 
     FROM Marking M 
     WHERE M.SubjectID = 'SubBio' 
       AND M.ExamID = 1) AS Bio,
    (SELECT TOP 1 M.SubjectObtMarks 
     FROM Marking M 
     WHERE M.SubjectID = 'SubIsl' 
       AND M.ExamID = 1) AS Isl,
    (SELECT TOP 1 SUM(Marking.SubjectObtMarks) 
     FROM Marking 
     WHERE ExamID = 1) AS ObtMarks
FROM 
    Exam, Student 
INNER JOIN 
    PrimaryData ON Student.StuRollNo = PrimaryData.StuID 
INNER JOIN 
    Section ON Section.SectionID = PrimaryData.SectionID 
WHERE 
    Section.SectionName = 'M1' 
    AND Exam.ExamID = 1;

Here's what it retrieves:

这是它检索的内容:

Database diagram:

Please help me resolve this problem. Thanks!

请帮我解决这个问题。谢谢!

1 个解决方案

#1

It's because you do not restrict which Student the Marking belongs to anywhere. So your query is essentially always picking up the first Marking, and not checking who this Marking belongs to.

这是因为您不限制标记所属的学生。所以你的查询基本上总是拿起第一个标记,而不是检查这个标记属于谁。

SELECT 
Student.StuRollNo, Student.StuName, Student.StuFName, 
(SELECT TOP 1 M.SubjectObtMarks 
 FROM Marking M 
 WHERE M.SubjectID = 'SubUrdu' 
   AND M.ExamID = 1
   AND M.StuID = S.StuID) AS Urdu
FROM 
    Exam, Student S
INNER JOIN 
    PrimaryData ON Student.StuRollNo = PrimaryData.StuID 
INNER JOIN 
    Section ON Section.SectionID = PrimaryData.SectionID