--try
declare @sql varchar(8000)
select @sql=''
select @sql=@sql+',[其中'+left(发放日期,4)+'数]=max(case left(发放日期,4) when '''+left(发放日期,4)+''' then 工资数 else 0 end)'
from 人员工资发放表
group by left(发放日期,4)
exec('select 人员=a.姓名,发放工资总数=isnull(sum(b.工资数),0)'+@sql+'
from 人员代码表 a left join 人员工资发放表 b on a.编码=b.编码
group by a.编码')
declare @sql varchar(8000)
select @sql=''
select @sql=@sql+',[其中'+left(发放日期,4)+'数]=max(case left(发放日期,4) when '''+left(发放日期,4)+''' then 工资数 else 0 end)'
from 人员工资发放表
group by left(发放日期,4)
exec('select 人员=a.姓名,发放工资总数=isnull(sum(b.工资数),0)'+@sql+'
from 人员代码表 a left join 人员工资发放表 b on a.编码=b.编码
group by a.编码')
select @sql=''
select @sql=@sql+',[其中'+left(发放日期,4)+'数]=sum(case left(发放日期,4) when '''+left(发放日期,4)+''' then 工资数 else 0 end)'
from #
group by left(发放日期,4)exec('select col_3'+@sql+' from # group by col_3')
exec('select 人员=a.姓名,发放工资总数=isnull(sum(b.工资数),0)'+@sql+'
from 人员代码表 a left join 人员工资发放表 b on a.编码=b.编码
group by a.编码')
declare @sql varchar(8000)
select @sql=''
select @sql=@sql+',[其中'+left(发放日期,4)+'数]=sum(case left(发放日期,4) when '''+left(发放日期,4)+''' then 工资数 else 0 end)'
from #
group by left(发放日期,4)
exec('select 人员=a.姓名,发放工资总数=isnull(sum(b.工资数),0)'+@sql+'
from 人员代码表 a left join 人员工资发放表 b on a.编码=b.编码
group by a.编码')
select f.pid,f.names,f.总工资,sum(p2.price)as pp,datepart(yy,p2.paydate) as dd from (
select a.pid,a.names,sum(price)as 总工资
from
#pp as a join #pp2 as b
on a.pid = b.pid
group by a.names,a.pid
) as f left join #pp as p
on f.names = p.names
right join #pp2 as p2
on f.pid = p2.pid
group by f.pid,f.names,f.总工资,datepart(yy,p2.paydate)
) as n
where dd = 2004
,isnull(sum(B.gzs),0) as '发放工资总计'
,sum(case when left(B.rq,4)='2005'
then B.gzs
else 0
end
) as '其中05年数'
from bm_ry A
left join rygz B on A.bm=B.bm
group by A.bm
select c.xm as 人员,sum(a.gzs)as 发放工资总计,b.gzs as 其中05年数
from rygz a,(select bm,sum(gzs) gzs from rygz where left(rq,4)='2005' group by bm )b,bm_ry c
where a.bm=b.bm and a.bm=c.bm group by a.bm,c.xm,b.gzs order by a.bm
select bm_ry.xm as 人员,
sum(isnull(rygz.gzs,0)) as 发放工资总计,
case when left(rq,4)='2005' then sum(isnull(rygz.gzs,0)) end as 其中05年数
from bm_ry,rygz
where bm_ry.bm *=rygz.bm
sum(isnull(rygz.gzs,0)) as 发放工资总计,
case when left(rygz.rq,4)='2005' then sum(isnull(rygz.gzs,0)) end as 其中05年数
from bm_ry,rygz
where bm_ry.bm *=rygz.bm