我如何将主题显示为列标题，以及学生姓名和总标记 [英] How can I show the subjects as column headers, as well as the student name and total mark
问题描述
我的Microsoft SQL Server Db中有4个表：主题，学生，考试和考试结果。
主题表列： SubjectID，SubjectName
学生表列： StudentID，Fname，Lname
考试表列： ExamID，ExamDate，ExamPeriod，Class
ExamResults列： ExamResultID，ExamID，StudentID，SubjectID，Result / p>
我正在使用以下查询来获取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 |结果| Fname | Lname
------------ + ----------- + -------- + --------- + - ---------
英文| 1 | 100 |认真的|贝克
数学| 2 | 35 |认真的|贝克
法语| 6 | 75 |认真的|贝克
英文| 1 | 56 |房子|理查兹
地理| 5 | 35 |房子|理查兹
法语| 6 | 75 |房子|理查兹
英文| 1 | 86 |雅各布| Jake
数学| 2 | 37 |雅各布| Jake
文学| 8 | 75 |雅各布| Jake
在5个科目中，我真正想要实现的是让表使用以下列标题并使用该格式：
Fname | Lname |英文|数学|地理|文学|法语| TotalScore
------- + ---------- + --------- + ------------- + - --------- + ------------ + -------- + -------------
认真|贝克| 100 | 35 | - | - | 75 | 210
房屋|理查兹56 | - | 35 | - | 75 | 166
雅各布|杰克| 86 | 37 | - | 75 | - | 198
我打算使用Subject.subjectID进行其他使用，不要在datagridview上显示它，所以假设它被隐藏。
这可能是因为我创建了表的方式，或者我需要更改表，还是需要查询更改。或者也许我需要在datagridview上以某种方式显示它？请帮助我，因为找不到解决方案。
您可以使用 PIVOT ：
SELECT pvt.Forename，
pvt.Surname，
pvt。[English]，
pvt。[数学]，
pvt。[地理]，
pvt。[文学]，
pvt。[法语]，
总分= ISNULL（pvt。[英文] ，0）+ ISNULL（pvt。[数学]，0）+ ISNULL（pvt。[Geography]，0）+ ISNULL（pvt。[文献]，0）+ ISNULL（pvt。[法语]，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考试作为e
打开er.ExamID = e.ExamID
LEFT JOIN学生AS su
ON er.StudentID = su.StudentID
WHERE e.Class = @Class
）AS e
PIVOT
（MAX（[Result]
FOR [Subject] IN（[英文]，[数学]，[地理]，[文学]，[法文]）
）AS pvt;
或条件聚合：
SELECT su.Fname，
su.Lname，
[英文] = MAX（CASE WHEN s.SubjectName ='English'THEN er.Result END），
[数学] = MAX（CASE WHEN s.SubjectName ='Mathematics'THEN er.Result END），
[Geography] = MAX（CASE WHEN s.SubjectName ='Geography'THEN er.Result END），
[文献] = MAX（CASE WHEN s.SubjectName ='Literature'THEN er.Result END），
[法语] = 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考试AS e
ON er.ExamID = e.ExamID
LEFT JOIN学生AS su
ON er.StudentID = su.StudentID
WHERE e.Class = @Class
GROUP BY su.Fname，su.Lname;
I have 4 tables in my Microsoft SQL Server Db: Subject, Student, Exam and ExamResults.
Subject table columns: SubjectID,SubjectName
Student table columns: StudentID,Fname,Lname
Exam table columns: ExamID,ExamDate,ExamPeriod,Class
ExamResults column: ExamResultID,ExamID,StudentID,SubjectID,Result
I am using the following query to get the results on a winforms datagridview (which I'm hosting on a wpf application):
"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";
I'm getting a result like this:
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
Out of the 5 subjects,what I really want to achieve is to have the table use the following column headers and use that format:
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
I intend to use Subject.subjectID for other use, not to display it on the datagridview, so assume it's hidden.
Is this possible given the the way I've created my tables, or do I need to change the tables, or is it the query that needs to change. Or maybe i need to display it in some way on the datagridview? Please help me because I can't find a solution.
You can achieve this using 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;
Or a conditional aggregate:
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;
这篇关于我如何将主题显示为列标题，以及学生姓名和总标记的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
