本人做一个工资查询记录,有员工3000人,工资项目约为30个,
员工工资表查询所需的表:
1、员工信息表 ygxxb
2、员工工资表 yggzb
表结构:员工编号(ygbh)工资项目编号 (gzxmbh),工资项目名称(gzxmmc),金额(je),发薪月份(fxyf)
如 :
0001 1 基职 267 2003-06-01
0001 2 工龄 20 2003-06-01
.
.
.
0002 1 基职 317 2003-06-01
0002 2 工龄 28 2003-06-01 3、职称表 diczcb
4、职务表 diczwb
5、残疾人员表 cjryb
本人欲查询相应的记录并将其横排过来。执行结果如下:
员工编号 姓名 基职 工龄
00001 张三 267 20
00002 里司 317 28
本人写了如下存储过程但执行起来其慢无比,希望能有高手指点迷津提高执行速度。
CREATE PROCEDURE P_GZBCX @cxtj nvarchar(4000) ,@startfxyf char(10),@endfxyf char(10) ,@lb char(1) AS -- 工资查询 @lb='h'为合计
/*declare @cxtj nvarchar(4000)
declare @startfxyf char(10)
declare @endfxyf char(10)
declare @lb char(1)
set @startfxyf='2003-03-01'
set @endfxyf='2003-03-01'
set @cxtj=' 员工编号 in (select ygbh from ZDNSRB where year(getdate())-nf=1)'
set @lb='h'
drop table #tmp_gzb
drop table #tmp_gzb1*/
declare @str1 NVARCHAR(4000)
declare @str4 NVARCHAR(4000)
declare @str6 NVARCHAR(4000)
set @str4=''
DECLARE @LS INT--列数
DECLARE @YGBH CHAR(5)
CREATE TABLE #tmp_gzb (
科室号 varchar(10),
科室名称 VARCHAR(50),
员工编号 CHAR(5),
姓名 varchar(10) ,
性别 char(2),
职务级别 varchar(50),
职称 varchar(50),
工资等级 VARCHAR(10),
残疾 char(2),
退休 char(2),
身份类别 varchar(50),
银行账号 varchar(50),
身份证号 varchar(50) ,
工资月份 char(7)
) ON [PRIMARY]
/*产生合计临时表*/
CREATE TABLE #tmp_gzb1(
科室名称 VARCHAR(50) COLLATE Chinese_PRC_CI_AS ,
) ON [PRIMARY]
declare @zdm varchar(20)
--DECLARE @YGBH CHAR(5)
DECLARE @YGXM VARCHAR(20)
DECLARE @JE VARCHAR(18)
DECLARE @GZBH INT
declare GZXM cursor
for
select distinct GZXMBH,GZXMMC from YGGZB where fxyf>=@startfxyf and fxyf<=@endfxyf ORDER BY GZXMBH--WHERE SCBZ='0' AND MYLRBZ='1'
open GZXM
SET @LS=1
fetch next from GZXM into @GZBH, @zdm
while(@@fetch_status<>-1)
begin
set @str4=@str4+'sum('+@zdm+') as '+@zdm+','
set @str1='alter table #tmp_gzb add '+ @zdm+' dec(18,2) default 0.00 WITH VALUES'
set @str6='alter table #tmp_gzb1 add '+ @zdm+' dec(18,2) default 0.00 WITH VALUES'
execute sp_executesql @str1
execute sp_executesql @str6
fetch next from GZXM into @GZBH, @zdm
SET @LS=@LS+1
end
close GZXM
DEALLOCATE GZXM
declare YGGZ cursor
for
SELECT dbo.YGXXB.EJKSbh,dbo.YGXXB.EJKSMC, dbo.YGXXB.YGBH, dbo.YGXXB.XM, ISNULL(dbo.YGXXB.XB,
'') , ISNULL(dbo.DICZWB.ZWMC, '') , ISNULL(dbo.DICZCB.ZCMC, '')
, CAST(dbo.YGXXB.GZDJ AS VARCHAR(10)) ,
(CASE WHEN CJRYB.YGBH IS NULL THEN '否' ELSE '是' END) CJBZ,
(CASE WHEN YGXXB.TXBZ='1' THEN '是' ELSE '否' END )AS TXBZ,
ISNULL(dbo.YGSFB.SFMC, ''),
ISNULL(dbo.YGXXB.YHZH, '') , ISNULL(dbo.YGXXB.SFZH, '') ,
CAST(A.JE AS VARCHAR(10)), A.GZXMMC,
substring(CONVERT(char(10), A.FXYF, 121) ,1,7) AS fxyf
FROM (SELECT * FROM dbo.YGGZB WHERE fxyf>=@startfxyf and fxyf<=@endfxyf ) A INNER JOIN
dbo.YGXXB ON A.YGBH = dbo.YGXXB.YGBH INNER JOIN
dbo.DICZWB ON dbo.YGXXB.ZWBH = dbo.DICZWB.ZWBH INNER JOIN
dbo.YGSFB ON dbo.YGXXB.SFLB = dbo.YGSFB.SFLB INNER JOIN
dbo.DICZCB ON dbo.YGXXB.ZCBH = dbo.DICZCB.ZCBH LEFT OUTER JOIN
dbo.CJRYB ON A.YGBH = dbo.CJRYB.YGBH ORDER BY YGXXB.YGBH,FXYF ASC
open YGGZ
DECLARE @XM VARCHAR(20)
DECLARE @XB VARCHAR(2)
DECLARE @ZW VARCHAR(50)
DECLARE @ZC VARCHAR(50)
DECLARE @GZDJ VARCHAR(10)--工资等级
DECLARE @CJBZ CHAR(2)--残疾标志
DECLARE @TXBZ CHAR(2)--退休标志
DECLARE @SFMC VARCHAR(50)
DECLARE @YHZH VARCHAR(50) --银行账号
DECLARE @SFZH VARCHAR(18)
DECLARE @KSMC VARCHAR(20)
DECLARE @FXYF VARCHAR(7)
DECLARE @BH1 CHAR(5)
DECLARE @STR2 NVARCHAR(4000)
DECLARE @STR3 NVARCHAR(4000)
DECLARE @STR NVARCHAR(4000)
DECLARE @FXYF1 char(7)
declare @ksh varchar(10)
SET @BH1=''
SET @FXYF1=''
fetch next from YGGZ into @ksh,@KSMC,@YGBH,@XM,@XB,@ZW,@ZC,@GZDJ,@CJBZ,@TXBZ,@SFMC,@YHZH,@SFZH,@JE,@ZDM ,@FXYF
while(@@fetch_status<>-1)
begin
IF @BH1<>@YGBH OR @FXYF<>@FXYF1--对不同的月份作不同纪录处理
BEGIN
IF @BH1<>''
BEGIN
--PRINT @STR
execute sp_executesql @str
END
SET @STR2= 'INSERT INTO #TMP_GZB (科室号,科室名称,员工编号,姓名,性别,职务级别,职称,工资等级,退休,残疾,身份类别,银行账号,身份证号,工资月份'+','+@ZDM+')'
SET @STR3=' VALUES('''+@KSh+''','''+@KSMC+''','''+@YGBH+''','''+@XM+''','''+@XB+''','''+@ZW+''','''+@ZC+''','''+@GZDJ+''','''+@TXBZ+''','''+@CJBZ+''','''+@SFMC+''','''+@YHZH+''','''+@SFZH+''','''+@FXYF+''','+@JE+')'
-- print '@str2='+@str2
-- print '@str3='+@str3
SET @STR=@STR2+@STR3
SET @BH1=@YGBH
SET @FXYF1=@FXYF
END
ELSE
BEGIN
SET @STR2=SUBSTRING(@STR2,1,LEN(@STR2)-1)+','+@ZDM+')'
SET @STR3=SUBSTRING(@STR3,1,LEN(@STR3)-1)+','+@JE+')'
SET @STR=@STR2+@STR3
END
fetch next from YGGZ into @KSh, @KSMC,@YGBH,@XM,@XB,@ZW,@ZC,@GZDJ,@CJBZ,@TXBZ,@SFMC,@YHZH,@SFZH,@JE,@ZDM,@FXYF
END
execute sp_executesql @str
close YGGZ
DEALLOCATE YGGZ
--declare @str7 nvarchar(4000)
--set @str7='select * from #tmp_gzb where '+@cxtj
--print @str7
--execute sp_executesql @str7
--print @lb
if @lb='h'
begin
declare @str5 nvarchar(4000)
set @str4=substring(@str4,1,len(@str4)-1)
set @str5=@str4
/*print @str5*/
set @str4='select 科室名称,'+@str4+' from #tmp_gzb where '+@cxtj+' group by 科室号, 科室名称 order by 科室号 asc'
set @str4='insert #tmp_gzb1 '+@str4
print @str4
execute sp_executesql @str4
/* set @str5='insert #tmp_gzb1 select ''合计'' as 科室名,'+@str5+' from #tmp_gzb where'+@cxtj
execute sp_executesql @str5
print @str5*/
select * from #tmp_gzb1
end
else
begin
declare @str7 nvarchar(4000)
set @str7='select * from #tmp_gzb where '+@cxtj
--print @str7
execute sp_executesql @str7
endGO
员工工资表查询所需的表:
1、员工信息表 ygxxb
2、员工工资表 yggzb
表结构:员工编号(ygbh)工资项目编号 (gzxmbh),工资项目名称(gzxmmc),金额(je),发薪月份(fxyf)
如 :
0001 1 基职 267 2003-06-01
0001 2 工龄 20 2003-06-01
.
.
.
0002 1 基职 317 2003-06-01
0002 2 工龄 28 2003-06-01 3、职称表 diczcb
4、职务表 diczwb
5、残疾人员表 cjryb
本人欲查询相应的记录并将其横排过来。执行结果如下:
员工编号 姓名 基职 工龄
00001 张三 267 20
00002 里司 317 28
本人写了如下存储过程但执行起来其慢无比,希望能有高手指点迷津提高执行速度。
CREATE PROCEDURE P_GZBCX @cxtj nvarchar(4000) ,@startfxyf char(10),@endfxyf char(10) ,@lb char(1) AS -- 工资查询 @lb='h'为合计
/*declare @cxtj nvarchar(4000)
declare @startfxyf char(10)
declare @endfxyf char(10)
declare @lb char(1)
set @startfxyf='2003-03-01'
set @endfxyf='2003-03-01'
set @cxtj=' 员工编号 in (select ygbh from ZDNSRB where year(getdate())-nf=1)'
set @lb='h'
drop table #tmp_gzb
drop table #tmp_gzb1*/
declare @str1 NVARCHAR(4000)
declare @str4 NVARCHAR(4000)
declare @str6 NVARCHAR(4000)
set @str4=''
DECLARE @LS INT--列数
DECLARE @YGBH CHAR(5)
CREATE TABLE #tmp_gzb (
科室号 varchar(10),
科室名称 VARCHAR(50),
员工编号 CHAR(5),
姓名 varchar(10) ,
性别 char(2),
职务级别 varchar(50),
职称 varchar(50),
工资等级 VARCHAR(10),
残疾 char(2),
退休 char(2),
身份类别 varchar(50),
银行账号 varchar(50),
身份证号 varchar(50) ,
工资月份 char(7)
) ON [PRIMARY]
/*产生合计临时表*/
CREATE TABLE #tmp_gzb1(
科室名称 VARCHAR(50) COLLATE Chinese_PRC_CI_AS ,
) ON [PRIMARY]
declare @zdm varchar(20)
--DECLARE @YGBH CHAR(5)
DECLARE @YGXM VARCHAR(20)
DECLARE @JE VARCHAR(18)
DECLARE @GZBH INT
declare GZXM cursor
for
select distinct GZXMBH,GZXMMC from YGGZB where fxyf>=@startfxyf and fxyf<=@endfxyf ORDER BY GZXMBH--WHERE SCBZ='0' AND MYLRBZ='1'
open GZXM
SET @LS=1
fetch next from GZXM into @GZBH, @zdm
while(@@fetch_status<>-1)
begin
set @str4=@str4+'sum('+@zdm+') as '+@zdm+','
set @str1='alter table #tmp_gzb add '+ @zdm+' dec(18,2) default 0.00 WITH VALUES'
set @str6='alter table #tmp_gzb1 add '+ @zdm+' dec(18,2) default 0.00 WITH VALUES'
execute sp_executesql @str1
execute sp_executesql @str6
fetch next from GZXM into @GZBH, @zdm
SET @LS=@LS+1
end
close GZXM
DEALLOCATE GZXM
declare YGGZ cursor
for
SELECT dbo.YGXXB.EJKSbh,dbo.YGXXB.EJKSMC, dbo.YGXXB.YGBH, dbo.YGXXB.XM, ISNULL(dbo.YGXXB.XB,
'') , ISNULL(dbo.DICZWB.ZWMC, '') , ISNULL(dbo.DICZCB.ZCMC, '')
, CAST(dbo.YGXXB.GZDJ AS VARCHAR(10)) ,
(CASE WHEN CJRYB.YGBH IS NULL THEN '否' ELSE '是' END) CJBZ,
(CASE WHEN YGXXB.TXBZ='1' THEN '是' ELSE '否' END )AS TXBZ,
ISNULL(dbo.YGSFB.SFMC, ''),
ISNULL(dbo.YGXXB.YHZH, '') , ISNULL(dbo.YGXXB.SFZH, '') ,
CAST(A.JE AS VARCHAR(10)), A.GZXMMC,
substring(CONVERT(char(10), A.FXYF, 121) ,1,7) AS fxyf
FROM (SELECT * FROM dbo.YGGZB WHERE fxyf>=@startfxyf and fxyf<=@endfxyf ) A INNER JOIN
dbo.YGXXB ON A.YGBH = dbo.YGXXB.YGBH INNER JOIN
dbo.DICZWB ON dbo.YGXXB.ZWBH = dbo.DICZWB.ZWBH INNER JOIN
dbo.YGSFB ON dbo.YGXXB.SFLB = dbo.YGSFB.SFLB INNER JOIN
dbo.DICZCB ON dbo.YGXXB.ZCBH = dbo.DICZCB.ZCBH LEFT OUTER JOIN
dbo.CJRYB ON A.YGBH = dbo.CJRYB.YGBH ORDER BY YGXXB.YGBH,FXYF ASC
open YGGZ
DECLARE @XM VARCHAR(20)
DECLARE @XB VARCHAR(2)
DECLARE @ZW VARCHAR(50)
DECLARE @ZC VARCHAR(50)
DECLARE @GZDJ VARCHAR(10)--工资等级
DECLARE @CJBZ CHAR(2)--残疾标志
DECLARE @TXBZ CHAR(2)--退休标志
DECLARE @SFMC VARCHAR(50)
DECLARE @YHZH VARCHAR(50) --银行账号
DECLARE @SFZH VARCHAR(18)
DECLARE @KSMC VARCHAR(20)
DECLARE @FXYF VARCHAR(7)
DECLARE @BH1 CHAR(5)
DECLARE @STR2 NVARCHAR(4000)
DECLARE @STR3 NVARCHAR(4000)
DECLARE @STR NVARCHAR(4000)
DECLARE @FXYF1 char(7)
declare @ksh varchar(10)
SET @BH1=''
SET @FXYF1=''
fetch next from YGGZ into @ksh,@KSMC,@YGBH,@XM,@XB,@ZW,@ZC,@GZDJ,@CJBZ,@TXBZ,@SFMC,@YHZH,@SFZH,@JE,@ZDM ,@FXYF
while(@@fetch_status<>-1)
begin
IF @BH1<>@YGBH OR @FXYF<>@FXYF1--对不同的月份作不同纪录处理
BEGIN
IF @BH1<>''
BEGIN
--PRINT @STR
execute sp_executesql @str
END
SET @STR2= 'INSERT INTO #TMP_GZB (科室号,科室名称,员工编号,姓名,性别,职务级别,职称,工资等级,退休,残疾,身份类别,银行账号,身份证号,工资月份'+','+@ZDM+')'
SET @STR3=' VALUES('''+@KSh+''','''+@KSMC+''','''+@YGBH+''','''+@XM+''','''+@XB+''','''+@ZW+''','''+@ZC+''','''+@GZDJ+''','''+@TXBZ+''','''+@CJBZ+''','''+@SFMC+''','''+@YHZH+''','''+@SFZH+''','''+@FXYF+''','+@JE+')'
-- print '@str2='+@str2
-- print '@str3='+@str3
SET @STR=@STR2+@STR3
SET @BH1=@YGBH
SET @FXYF1=@FXYF
END
ELSE
BEGIN
SET @STR2=SUBSTRING(@STR2,1,LEN(@STR2)-1)+','+@ZDM+')'
SET @STR3=SUBSTRING(@STR3,1,LEN(@STR3)-1)+','+@JE+')'
SET @STR=@STR2+@STR3
END
fetch next from YGGZ into @KSh, @KSMC,@YGBH,@XM,@XB,@ZW,@ZC,@GZDJ,@CJBZ,@TXBZ,@SFMC,@YHZH,@SFZH,@JE,@ZDM,@FXYF
END
execute sp_executesql @str
close YGGZ
DEALLOCATE YGGZ
--declare @str7 nvarchar(4000)
--set @str7='select * from #tmp_gzb where '+@cxtj
--print @str7
--execute sp_executesql @str7
--print @lb
if @lb='h'
begin
declare @str5 nvarchar(4000)
set @str4=substring(@str4,1,len(@str4)-1)
set @str5=@str4
/*print @str5*/
set @str4='select 科室名称,'+@str4+' from #tmp_gzb where '+@cxtj+' group by 科室号, 科室名称 order by 科室号 asc'
set @str4='insert #tmp_gzb1 '+@str4
print @str4
execute sp_executesql @str4
/* set @str5='insert #tmp_gzb1 select ''合计'' as 科室名,'+@str5+' from #tmp_gzb where'+@cxtj
execute sp_executesql @str5
print @str5*/
select * from #tmp_gzb1
end
else
begin
declare @str7 nvarchar(4000)
set @str7='select * from #tmp_gzb where '+@cxtj
--print @str7
execute sp_executesql @str7
endGO
查询时最好多加限制条件
使生成的数据集尽可能的小我曾试在用SQL语名在5000条记录的表中选出10条记录进行操作
一点也感觉不到有延时