drop table #Temp_EA
select top 0 OrganizationalNum2,OrganizationalNum3,OrganizationalNum4,EmployeeNum,MisEmployeeNum,JobsNum,PLNum,PostsNum,PostsName,JobCategories,IsPI into #Temp_EA from dbo.T_H_EmployeeAttribution
insert into #Temp_EA select b.OrganizationalNum2,b.OrganizationalNum3,b.OrganizationalNum4,b.EmployeeNum,b.MisEmployeeNum,b.JobsNum,b.PLNum,b.PostsNum,b.PostsName,b.JobCategories,b.IsPI
from dbo.T_E_EmployeePieceworkItemManagerBounty a
inner join dbo.T_H_EmployeeAttribution b
on b.EmployeeNum=a.EmployeeNum
where a.DataTime='2010-05-01'
declare @PieceworkItemNameList varchar(max),@PieceworkItemNameList1 varchar(max),@SQL varchar(max)
set @PieceworkItemNameList=''
select @PieceworkItemNameList=@PieceworkItemNameList+PieceworkItemName+',' from T_H_ParameterPieceworkItemManager whereDataTime='2010-05-01' and PLevel=1
set @PieceworkItemNameList=left(@PieceworkItemNameList,len(@PieceworkItemNameList)-1)
set @PieceworkItemNameList1=@PieceworkItemNameList
set @PieceworkItemNameList='['+ replace(@PieceworkItemNameList,',','],[')+']'
set@SQL='select MisEmployeeNum MIS员工编号,EmployeeNum 员工编号,EmployeeName 姓名,Sex 性别,'+@PieceworkItemNameList1 + ' from (select a.EmployeeName,a.MisEmployeeNum,a.EmployeeNum,a.Sex,c.PieceworkItemName,b.Bounty from
T_E_Employee a
inner join#Temp_EA d on a.EmployeeNum=d.EmployeeNum
left join T_E_EmployeePieceworkItemManagerBounty b on a.EmployeeNum=b.EmployeeNum
left join T_H_ParameterPieceworkItemManager c on b.PIMID=c.PIMID and c.DataTime=''2010-05-01'' and c.PLevel=1
inner join T_D_OrganizationalStructure x on a.OrganizationalNum2=x.OrganizationalNum
left join T_D_OrganizationalStructure y on a.OrganizationalNum3=y.OrganizationalNum
where d.PostsName=''客户经理'') apivot (min(Bounty) for PieceworkItemName in ('+ @PieceworkItemNameList +')) b'
print@SQL
exec (@SQL)
pivot是分组，为什么我查出来的总额却不是正确的，我这样写有错误吗？
|
