Select s.SID , s.Lastname, s.Firstname, s.MI, g.YearLevel, g.Course,
count(s.SID) As 'Number Of Passed Subject'
From student s
inner Join grades g on g.StudentID = s.SID
Where g.Completion = 'Passed' and g.YearLevel = '4th Year'
group by g.YearLevel, g.Course
order by g.YearLevel, g.Course;
Select s1.SID , s1.Lastname, s1.Firstname, s1.MI, g1.YearLevel, g1.Course,
SUM(su.LecUnit) As 'Total of Units Passed'
From grades g1
INNER Join student s1 ON s1.SID = g1.StudentID
INNER Join subjects su ON su.SubjectCode = g1.SubjectCode
WHERE g1.Completion = 'Passed' AND g1.YearLevel = '4th Year'
GROUP BY g1.Course, g1.YearLevel
order by g1.Course, g1.YearLevel;
uj5u.com熱心網友回復：
使用第二個查詢作為起點，然后為通過科目的數量添加不同的學生課程計數。這里的邏輯是對subjects表的附加連接可能會復制前兩個連接中的每個中間記錄。采用不同的計數消除了這種可能的重復。
Select s1.SID, s1.Lastname, s1.Firstname, s1.MI,
COUNT(DISTINCT g1.Course) As `Number Of Passed Subject`,
SUM(su.LecUnit) As `Total of Units Passed`
FROM student s1
INNER JOIN grades g1 ON s1.SID = g1.StudentID
INNER JOIN subjects su ON su.SubjectCode = g1.SubjectCode
WHERE g1.Completion = 'Passed' AND g1.YearLevel = '4th Year'
GROUP BY 1
ORDER BY 1;
請注意，我也僅按 分組s1.SID。假設SID是該表中的主鍵，它涵蓋了表中的所有列student。您選擇的其他表欄位可能不屬于那里。
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/438120.html
標籤：mysql