c# - 如何将科目显示为列标题以及学生姓名和总分

我的Microsoft SQL Server Db中有4个表：主题，学生，考试和ExamResults。

主题表列：SubjectID，SubjectName

学生表列：StudentID，Fname，Lname

考试表栏：ExamID，ExamDate，ExamPeriod，Class

ExamResults栏：ExamResultID，ExamID，StudentID，SubjectID，结果

我正在使用以下查询在winforms datagridview（我在wpf应用程序上托管）上获取结果：

"SELECT SubjectName, Subject.SubjectID,Result, Fname, Lname FROM Subject "
+"LEFT JOIN ExamResults ON Subject.SubjectID=ExamResults.SubjectID "
+"LEFT JOIN Exam ON ExamResults.ExamID=Exam.ExamID "
+"LEFT JOIN Student on ExamResults.StudentID=Student.StudentID "
+"WHERE Exam.Class=@Class";

我得到这样的结果：

SubjectName | SubjectID | Result |  Fname  | Lname
------------+-----------+--------+---------+----------
English     |     1     |   100  | Earnest | Baker
Mathematics |     2     |    35  | Earnest | Baker
French      |     6     |    75  | Earnest | Baker
English     |     1     |    56  |  House  | Richards
Geography   |     5     |    35  |  House  | Richards
French      |     6     |    75  |  House  | Richards
English     |     1     |    86  |  Jacob  | Jake
Mathematics |     2     |    37  |  Jacob  | Jake
Literature  |     8     |    75  |  Jacob  | Jake

在这5个主题中，我真正想要实现的是让表使用以下列标题并使用该格式：

 Fname  |   Lname  | English | Mathematics | Geography | Literature | French | TotalScore
 -------+----------+---------+-------------+-----------+------------+--------+-------------
Earnest |   Baker  |   100   |     35      |      -    |     -      |   75   |     210
 House  | Richards |    56   |      -      |     35    |     -      |   75   |     166
 Jacob  |    Jake  |    86   |     37      |      -    |     75     |    -   |     198

我打算将Subject.subjectID用于其他用途，而不是将其显示在datagridview上，因此假定它是隐藏的。

考虑到我创建表的方式是否可行，还是需要更改表，还是需要更改的查询？还是我需要以某种方式在datagridview上显示它？请帮我，因为我找不到解决方案。

最佳答案

您可以使用PIVOT实现此目的：

SELECT  pvt.Forename,
        pvt.Surname,
        pvt.[English],
        pvt.[Mathematics],
        pvt.[Geography],
        pvt.[Literature],
        pvt.[French],
        TotalScore = ISNULL(pvt.[English], 0) + ISNULL(pvt.[Mathematics], 0) + ISNULL(pvt.[Geography], 0) + ISNULL(pvt.[Literature], 0) + ISNULL(pvt.[French], 0)
FROM    (   SELECT  s.SubjectName, er.Result, su.Fname, su.Lname
            FROM    Subject AS s
                    INNER JOIN ExamResults AS er
                        ON s.SubjectID = er.SubjectID
                    INNER JOIN Exam AS e
                        ON er.ExamID = e.ExamID
                    LEFT JOIN Student AS su
                        ON er.StudentID = su.StudentID
            WHERE   e.Class = @Class
        ) AS e
        PIVOT
        (   MAX([Result]
            FOR [Subject] IN ([English], [Mathematics], [Geography], [Literature], [French])
        ) AS pvt;

或条件汇总：

SELECT  su.Fname,
        su.Lname ,
        [English] = MAX(CASE WHEN s.SubjectName = 'English' THEN er.Result END),
        [Mathematics] = MAX(CASE WHEN s.SubjectName = 'Mathematics' THEN er.Result END),
        [Geography] = MAX(CASE WHEN s.SubjectName = 'Geography' THEN er.Result END),
        [Literature] = MAX(CASE WHEN s.SubjectName = 'Literature' THEN er.Result END),
        [French] = MAX(CASE WHEN s.SubjectName = 'French' THEN er.Result END),
        TotalScore = SUM(er.Result)
FROM    Subject AS s
        INNER JOIN ExamResults AS er
            ON s.SubjectID = er.SubjectID
        INNER JOIN Exam AS e
            ON er.ExamID = e.ExamID
        LEFT JOIN Student AS su
            ON er.StudentID = su.StudentID
WHERE   e.Class = @Class
GROUP BY su.Fname, su.Lname;