表a
empidnamechnxingbie
表b
empidPayDcAmt yearmm
select a.empid as id,a.namechn,heji='0',yi=max(case b.yearmm when '200701'then b.PayDcAmt else 0 end ),
er=max(case b.yearmm when '200702'then b.PayDcAmt else 0 end ),
san=max(case b.yearmm when '200703'then b.PayDcAmt else 0 end ),
si=max(case b.yearmm when '200704'then b.PayDcAmt else 0 end ),
wu=max(case b.yearmm when '200705'then b.PayDcAmt else 0 end ),
liu=max(case b.yearmm when '200706'then b.PayDcAmt else 0 end ),
qi=max(case b.yearmm when '200707'then b.PayDcAmt else 0 end ),
ba=max(case b.yearmm when '200708'then b.PayDcAmt else 0 end ),
jiu=max(case b.yearmm when '200709'then b.PayDcAmt else 0 end ),
shi=max(case b.yearmm when '200710'then b.PayDcAmt else 0 end ),
sy=max(case b.yearmm when '200711'then b.PayDcAmt else 0 end ),
sr=max(case b.yearmm when '200712'then b.PayDcAmt else 0 end )
froma left join b on a.empid=b.empid
where substring(yearmm,1,4)='2007'
group by a.empid ,a.namechn
得出的结果是
empidnamechn yiersansiwuliuqibajiushisysr
1
张三 100 100 100100 100 100100 100 100100 100 100
2
张四 100 100 100100 100 100100 100 100100 100 100
我要后面加一行 合计怎么加啊
|
