Coding 路歹行

有三個Table

Student [ StudentID,StudentName,ClassID ]

Teacher [ TeacherID , TeacherName , ClassID ]

Exam [ ExamName,StudentID,Subject,Score ]

1.使用SQL列出每個班級的老師名稱與學生數量

Select t.TeacherName,COUNT(s.StudentID) as StudentCount
From Student s left JOIN Teacher t ON t.ClassID = s.ClassID
group by t.TeacherName

第一題較為簡單，唯一要注意的是Group by的用法

使用Group by的欄位，在Select的地方要嘛就是聚合函數 ex.SUM()、Count()等等，不然就一定要是下方有Group by到的欄位，因為每個Group只會有一個輸出

舉例.如果上方的例子，Select 要再加入TeacherID的欄位，那下方Group by之後也必須加入這個欄位，否則會出錯

 

2.使用SQL列出ExamName為「第一次段考」的考試，各班級總分相加第一名的學生名稱與總分

這題先用到Group by 找出「第一次段考」，算出每位學生的分數總合

 Select s.StudentID,s.StudentName,s.ClassID,SUM(e.Score) as Total
  From ExamScore e join Student s on e.StudentID = s.StudentID
  Where e.ExamName = '第一次月考'
  Group by s.StudentID,s.StudentName,s.ClassID

 

接著要從上面的Table用MAX()找出每班的最大值

Select t.ClassID,MAX(t.Total) as ExamTotal
From(

  Select s.StudentID,s.StudentName,s.ClassID,SUM(e.Score) as Total
  From ExamScore e join Student s on e.StudentID = s.StudentID
  Where e.ExamName = '第一次月考'
  Group by s.StudentID,s.StudentName,s.ClassID

) t Group by t.ClassID

這邊要注意，因為使用Group by把每個班級當作輸出，搭配MAX函式找出最大值，所以Select就不能再加入原有的學生名稱欄位

故要再用一層Select重新包起來，把學生名稱的欄位加進去，概念如下

select b.* from( select id,max(value) as max_value from table group by id) a left join table b on a.id=b.id and a.max_value=b.valus

結果如下：

Select b.*
From (

Select t.ClassID,MAX(t.Total) as ExamTotal
From(

  Select s.StudentID,s.StudentName,s.ClassID,SUM(e.Score) as Total
  From ExamScore e join Student s on e.StudentID = s.StudentID
  Where e.ExamName = '第一次月考'
  Group by s.StudentID,s.StudentName,s.ClassID

) t Group by t.ClassID

 ) a Left join 
  
  (  Select s.StudentID,s.StudentName,s.ClassID,SUM(e.Score) as Total
  From ExamScore e join Student s on e.StudentID = s.StudentID
  Where e.ExamName = '第一次月考'
  Group by s.StudentID,s.StudentName,s.ClassID  ) b

ON a.ClassID = b.ClassID AND a.ExamTotal = b.Total

參考資料：GROUP BY with MAX(DATE) [duplicate]