我的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;

10-06 05:17