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

Question

我的 Microsoft SQL Server 数据库中有 4 个表:Subject、Student、Exam 和 ExamResults。

主题表列:SubjectID,SubjectName

学生表列:StudentID,Fname,Lname

考试表列:ExamID、ExamDate、ExamPeriod、Class

ExamResults 列:ExamResultID,ExamID,StudentID,SubjectID,Result

我正在使用以下查询在 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 上显示它？请帮助我，因为我找不到解决方案。

Answer 1

您可以使用 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;