pam_payinfo_detail 是薪资明细表
Sys_Users 是员工表
select B.name,
'attend_days1 ' = (case A.month when '01 ' then sum(isnull(attend_days,0)) else 0 end),
'attend_days2 ' = (case A.month when '02 ' then sum(isnull(attend_days,0)) else 0 end),
'attend_days3 ' = (case A.month when '03 ' then sum(isnull(attend_days,0)) else 0 end),
'attend_days4 ' = (case A.month when '04 ' then sum(isnull(attend_days,0)) else 0 end),
'attend_days5 ' = (case A.month when '05 ' then sum(isnull(attend_days,0)) else 0 end),
'attend_days6 ' = (case A.month when '06 ' then sum(isnull(attend_days,0)) else 0 end),
'attend_days7 ' = (case A.month when '07 ' then sum(isnull(attend_days,0)) else 0 end),
'attend_days8 ' = (case A.month when '08 ' then sum(isnull(attend_days,0)) else 0 end),
'attend_days9 ' = (case A.month when '09 ' then sum(isnull(attend_days,0)) else 0 end),
'attend_days10 ' = (case A.month when '10 ' then sum(isnull(attend_days,0)) else 0 end),
'attend_days11 ' = (case A.month when '11 ' then sum(isnull(attend_days,0)) else 0 end),
'attend_days12 ' = (case A.month when '12 ' then sum(isnull(attend_days,0)) else 0 end)
from pam_payinfo_detail A
join Sys_Users B On A.userid = B.userid
Group BY A.userid,
B.name,
C.Year_month
这是想实现
姓名 一月,二月,三月,四月....这种效果,但很明显我的语句是检查不过去的
报告:选择列表中的列 'pam_payinfo_detail.month ' 无效，因为该列没有包含在聚合函数或 GROUP BY 子句中。
请高手帮帮忙吧没分了,谢谢谢谢谢谢谢谢
------解决方案--------------------
-- sum 及 isnull 放在 case when 外面
'attend_days_xx ' = sum(isnull(case A.month when 'xx ' then attend_days,0 else 0 end),
------解决方案--------------------
try
select B.name,
'attend_days1 ' = sum(case A.[month] when '01 ' then isnull(attend_days,0) else 0 end),
'attend_days2 ' = sum(case A.[month] when '02 ' then isnull(attend_days,0) else 0 end),
'attend_days3 ' = sum(case A.[month] when '03 ' then isnull(attend_days,0) else 0 end),
'attend_days4 ' = sum(case A.[month] when '04 ' then isnull(attend_days,0) else 0 end),
'attend_days5 ' = sum(case A.[month] when '05 ' then isnull(attend_days,0) else 0 end),
'attend_days6 ' = sum(case A.[month] when '06 ' then isnull(attend_days,0) else 0 end),
'attend_days7 ' = sum(case A.[month] when '07 ' then isnull(attend_days,0) else 0 end),
'attend_days8 ' = sum(case A.[month] when '08 ' then isnull(attend_days,0) else 0 end),