我有這個查詢顯示以下結果，我想使用這個 MarksObtained 并取出每門課程的 min、max 和 avg 并與第二個查詢一起顯示（在下面提供）。
select
CourseName, StdID, MarksObtained
from
stdmarks
inner join
course on course.courseid = stdmarks.examid
-------------------------- ------- ---------------
| CourseName | StdID | MarksObtained |
-------------------------- ------- ---------------
| Digital Logic | 1 | 20 |
| Visual Prog | 1 | 20 |
| Computer Arch and Design | 1 | 20 |
| Digital Logic | 2 | 20 |
| Visual Prog | 2 | 20 |
-------------------------- ------- ---------------
這是第二個查詢
select
distinct CourseName, TeacherName, SemName
from
teacher
inner join
stdcourseteacher on teacher.teacherid = stdcourseteacher.teacherid
inner join
course on course.courseid = stdcourseteacher.courseid
inner join
semester on stdcourseteacher.semid = semester.semid
------------------------- ------------- ----------
| CourseName | TeacherName | SemName |
------------------------- ------------- ----------
| Business Communications | Dr. Iman | Fall2021 |
| Calculus - 1 | Dr. Khalid | Fall2021 |
| Calculus - 2 | Dr. Khalid | Fall2020 |
------------------------- ------------- ----------
所以它基本上會顯示學生完成的每門課程的最小值、最大值和平均值。
我想要的是：
------------------------- ------------- ---------- ----- ----- -----
| CourseName | TeacherName | SemName | Min | Max | Avg |
------------------------- ------------- ---------- ----- ----- -----
| Business Communications | Dr. Iman | Fall2021 | 80 | 20 | 50 |
| Calculus - 1 | Dr. Khalid | Fall2021 | 70 | 15 | 45 |
| Calculus - 2 | Dr. Khalid | Fall2020 | 85 | 15 | 50 |
------------------------- ------------- ---------- ----- ----- -----
樣本資料：
標準標記表：
------- -------- ---------------
| StdID | ExamID | MarksObtained |
------- -------- ---------------
| 1 | 9 | 20 |
| 1 | 10 | 20 |
| 1 | 11 | 20 |
------- -------- ---------------
StdCourseTeacher 表：
------- ---------- ------------ -------
| StdID | CourseID | TeacherID | SemID |
------- ---------- ------------ -------
| 1 | 9 | 7 | 6 |
| 1 | 10 | 7 | 6 |
| 1 | 11 | 2 | 6 |
| 2 | 9 | 7 | 6 |
| 2 | 10 | 7 | 6 |
------- ---------- ------------ -------
考試表：
-------- -------- ---------- ---------- ------- ---------- -----------
| ExamID | EvalID | Topic | MaxMarks | SemID | CourseID | TeacherID |
-------- -------- ---------- ---------- ------- ---------- -----------
| 1 | 3 | Mid-Term | 20 | 6 | 1 | 3 |
| 2 | 3 | Mid-Term | 20 | 6 | 2 | 4 |
| 3 | 3 | Mid-Term | 20 | 6 | 3 | 7 |
-------- -------- ---------- ---------- ------- ---------- -----------
課程表：
---------- --------------------------- ----------
| CourseID | CourseName | Semester |
---------- --------------------------- ----------
| 1 | Calculus - 1 | 1 |
| 2 | Business Communications | 1 |
| 3 | Introduction To Computing | 1 |
---------- --------------------------- ----------
學期表：
------- ------------
| SemID | SemName |
------- ------------
| 1 | Spring2020 |
| 2 | Summer2020 |
------- ------------
教師表：
----------- -------------
| TeacherID | TeacherName |
----------- -------------
| 2 | Dr. Ahmed |
| 3 | Dr. Khalid |
----------- -------------
uj5u.com熱心網友回復：
我認為您想group by使用以下聚合函式：
select CourseName, TeacherName, SemName, min(MarksObtained), Max(MarksObtained), avg(MarksObtained)
from teacher T
inner join CT on CT.teacherid = T.teacherid
inner join course C on C.courseid = CT.courseid
inner join semester S on S.semid = CT.semid
inner join stdmarks M on M.examid = C.courseid
group by CourseName, TeacherName, SemName
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/363708.html
標籤：sql sql-server 查询语句