我写了以下sql查询
SELECT ST.Roll, CR.Code, CR.Title, CR.Credits, SCH.ObtainedGPA
FROM [MIU_UCAM.1.0.1].[dbo].[StudentCourseHistory] AS SCH
JOIN [MIU_UCAM.1.0.1].[dbo].[Student] AS ST ON SCH.StudentID = ST.StudentID
JOIN [MIU_UCAM.1.0.1].[dbo].[Course] AS CR ON SCH.CourseID = CR.CourseID AND SCH.VersionID = CR.VersionID
WHERE ST.Roll ='0914BPM00387'
返回如下结果
Roll Code Title Credits ObtainedGPA
0914BPM00387 BPM216 Physiology Lab - 1 4.00
0914BPM00387 BPM217 Pharmaceutical Microbiology 3 2.25
0914BPM00387 BPM217 Pharmaceutical Microbiology 3 2.00
0914BPM00387 BPM218 Pharmaceutical Microbiology Lab 1 3.50
这里的BPM217和BPM217相同,但是它的ObtainedGrade分别为2.25和2.00。现在如何从这两行中仅获取最大值2.25。
我需要sql查询。谢谢先进。
SELECT ST.Roll, CR.Code, CR.Title, CR.Credits, MAX(SCH.ObtainedGPA)
FROM [MIU_UCAM.1.0.1].[dbo].[StudentCourseHistory] AS SCH
JOIN [MIU_UCAM.1.0.1].[dbo].[Student] AS ST ON SCH.StudentID = ST.StudentID
JOIN [MIU_UCAM.1.0.1].[dbo].[Course] AS CR ON SCH.CourseID = CR.CourseID AND SCH.VersionID = CR.VersionID
WHERE ST.Roll ='0914BPM00387'
GROUP BY ST.Roll, CR.Code, CR.Title, CR.Credits
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句