谁帮我把下面的SQL语句修改成存储过程,或是新增一个函数?谢谢大家了!!!急用!
declare @yymm varchar(6)
declare @emptypename varchar(1000)
declare @emptypetotal varchar(1000)
declare @sql varchar(4000)set @yymm='201208'
select @emptypetotal=isnull(@emptypetotal+'+','')+'['+itemname+']' from syssetlist where typeid='rs01'
select @emptypename=isnull(@emptypename+',','')+'['+itemname+']' from syssetlist where typeid='rs01'
set @sql='select [一级部门],[二级部门],[三级部门],[四级部门],'+@emptypename+',('+@emptypetotal+') as [总计] from
(
select
e.partname as [一级部门],d1.partname as [二级部门],d2.partname as [三级部门],
d3.partname as [四级部门],e.emptypename,count(e.emptypename) as empnum,
from
peremployee e left join perdepart d on e.partno=d.partno
left join perdepart d1 on e.partno=d1.dtid2
left join perdepart d2 on e.partno=d2.dtid3
left join perdepart d3 on e.partno=d3.dtid4
where
exists(select * from perempmonthdoc em where em.empid=e.empid and em.yymm='''+@yymm+''')
and (d.dtid2!='' or d.dtid3!='' or d.dtid4!='')
group by e.partname,d1.partname,d2.partname,d3.partname,d4.partname,e.emptypename
) as S PIVOT(sum(empnum) for emptypename in('+@emptypename+')) as D'
exec(@sql)
最终效果:一级部门 部门 子部门 明细部门 职员 员工 临时工 实习生 试用工 正式工 总计
declare @yymm varchar(6)
declare @emptypename varchar(1000)
declare @emptypetotal varchar(1000)
declare @sql varchar(4000)set @yymm='201208'
select @emptypetotal=isnull(@emptypetotal+'+','')+'['+itemname+']' from syssetlist where typeid='rs01'
select @emptypename=isnull(@emptypename+',','')+'['+itemname+']' from syssetlist where typeid='rs01'
set @sql='select [一级部门],[二级部门],[三级部门],[四级部门],'+@emptypename+',('+@emptypetotal+') as [总计] from
(
select
e.partname as [一级部门],d1.partname as [二级部门],d2.partname as [三级部门],
d3.partname as [四级部门],e.emptypename,count(e.emptypename) as empnum,
from
peremployee e left join perdepart d on e.partno=d.partno
left join perdepart d1 on e.partno=d1.dtid2
left join perdepart d2 on e.partno=d2.dtid3
left join perdepart d3 on e.partno=d3.dtid4
where
exists(select * from perempmonthdoc em where em.empid=e.empid and em.yymm='''+@yymm+''')
and (d.dtid2!='' or d.dtid3!='' or d.dtid4!='')
group by e.partname,d1.partname,d2.partname,d3.partname,d4.partname,e.emptypename
) as S PIVOT(sum(empnum) for emptypename in('+@emptypename+')) as D'
exec(@sql)
最终效果:一级部门 部门 子部门 明细部门 职员 员工 临时工 实习生 试用工 正式工 总计
解决方案 »
- 点击按钮存储,如果失败回滚怎么写。
- decimal日期取前一天
- nvarchar 类型字段存数值的排序问题
- 在vc中用ado打开一个sql数据库?在线等待中。。。
- 求从数据库中读出日期是今天的记录的sql语句
- 如何将Excel表的含有数字和字符的列,使用sql语句,倒入到SQL Server?
- 我想用visual foxpro 编一个图书管理程序,可是我才学了一个月,不知道应该怎么样入手,基本的想法已经有了~~~~那位高手可以帮一下~~谢谢
- SQL server6.5那里可以下载?(急需这古老的东西)
- 请教SQL语句
- delphi程序怎么做到备份数据库?
- c++ mysql load data infile 无法导入数据
- Sql Server2008 碰到一个奇怪的问题 查询结果与查询条件不相符
create proc test
( @yymm varchar(6)='201208',@emptypename varchar(1000),@emptypetotal varchar(1000))
as
begin
declare @sql varchar(4000)select @emptypetotal=isnull(@emptypetotal+'+','')+'['+itemname+']' from syssetlist where typeid='rs01'
select @emptypename=isnull(@emptypename+',','')+'['+itemname+']' from syssetlist where typeid='rs01'
set @sql='select [一级部门],[二级部门],[三级部门],[四级部门],'+@emptypename+',('+@emptypetotal+') as [总计] from
(
select
e.partname as [一级部门],d1.partname as [二级部门],d2.partname as [三级部门],
d3.partname as [四级部门],e.emptypename,count(e.emptypename) as empnum,
from
peremployee e left join perdepart d on e.partno=d.partno
left join perdepart d1 on e.partno=d1.dtid2
left join perdepart d2 on e.partno=d2.dtid3
left join perdepart d3 on e.partno=d3.dtid4
where
exists(select * from perempmonthdoc em where em.empid=e.empid and em.yymm='''+@yymm+''')
and (d.dtid2!='' or d.dtid3!='' or d.dtid4!='')
group by e.partname,d1.partname,d2.partname,d3.partname,d4.partname,e.emptypename
) as S PIVOT(sum(empnum) for emptypename in('+@emptypename+')) as D'
exec(@sql)
end