select

[部门名称] = c.name,

 

[工号] = b.code,

 

[姓名] = b.cnname,

 

[到职日期] = b.jobdate,

 

[离职日期] = b.lastworkdate,

 

[大过] = sum(case when d.name='大过' then a.times  else 0 end ),

 

[小过] = sum(case when d.name='小过' then 1  else 0 end ) ,

 

[警告] = sum(case when d.name='警告' then 1  else 0 end ) ,

 

[大功] = sum(case when d.name='大功' then 1  else 0 end ) ,

 

[小功] = sum(case when d.name='小功' then 1  else 0 end ) ,

 

[嘉奖] = sum(case when d.name='嘉奖' then 1  else 0 end )

from EmployeeRewards a

left join employee b on a.EmployeeId=b.EmployeeId

left join department c on c.departmentId=b.departmentId

left join EmployeeRewardsItem d on d.EmployeeRewardsItemId=a.EmployeeRewardsItemId

group by  c.name,b.code,b.cnname,b.jobdate,b.lastworkdate