SELECT a.type, b.postnature, b.compcode
FROM 职工岗位管理情况表 AS b INNER JOIN 在职职工基本信息表 AS a ON b.name = a.statusname
WHERE (a.move = '在岗') and (type IN (SELECT name FROM 在职职工基本信息表用工类别维护表)) AND (postnature IN (SELECT name FROM 职工岗位性质维护表))
如果数据量很小，希望能做到以上的执行结果。
但是如果数据量大，目前最少已有50000条记录，数据库为sqlserver2005,请问该如何优化sql语句或者如何写这个效果。
[b]问题补充：[/b]
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
alter proc zgrytj (@username varchar(100),@tablename varchar(50))
as begin
declare
@qycode varchar(100),
@rolename varchar(100),
@gwlb varchar(100),
@gwxz varchar(100),
@rcount int,
@ctotalcount int,
@rtotalcount int,
@rname varchar(50),
@cname varchar(50),
@totalcount int,
@sqlstr nvarchar(4000),
@sqlstrI nvarchar(4000),
@sqlstrII nvarchar(4000)
create table #test(
rcount int,
ctotalcount int,
rtotalcount int,
rname varchar(50),
cname varchar(50),
)
select @qycode=b.code from sys_user a,sys_企业信息表 b
where a.corporation=b.code and a.username=@username
select @rolename=rolename from sys_memberofrole where member=@username
-----------------如果不是系统帐号查询出单条的信息------
if @rolename<>'系统管理员' and @rolename<>'省局领导'
begin
--申明游标
declare gwlb CURSOR FOR select name from 在职职工基本信息表用工类别维护表 order by id
--打开游标
open gwlb
fetch from gwlb into @gwlb
WHILE @@FETCH_STATUS = 0
begin
declare gwxz cursor for select name from 职工岗位性质维护表 order by id
open gwxz
fetch from gwxz into @gwxz
WHILE @@FETCH_STATUS = 0
begin
select @sqlstr='select @a=count(*) from '+@tablename+' where '+
' type ='''+@gwlb+''' and postnature='''+@gwxz+''' and compcode='''+@qycode+''''
select @sqlstrI='select @a=count(*) from '+@tablename+' where '+
' type ='''+@gwlb+''' and compcode='''+@qycode+''''
select @sqlstrII='select @a=count(*) from '+@tablename+' where '+
' postnature ='''+@gwxz+''' and compcode='''+@qycode+''''
exec sp_executesql @sqlstr,N'@a int output',@rcount output
exec sp_executesql @sqlstrI,N'@a int output',@rtotalcount output
exec sp_executesql @sqlstrII,N'@a int output',@ctotalcount output
insert into #test
values(@rcount,@ctotalcount,@rtotalcount,@gwlb,@gwxz)
fetch next from gwxz into @gwxz
end
close gwxz
deallocate gwxz
fetch next from gwlb into @gwlb
end
CLOSE gwlb--关闭游标
DEALLOCATE gwlb--释放游标
select * from #test
end
else
begin
--申明游标
declare gwlb CURSOR FOR select name from 在职职工基本信息表用工类别维护表 order by id
--打开游标
open gwlb
fetch from gwlb into @gwlb
WHILE @@FETCH_STATUS = 0
begin
declare gwxz cursor for select name from 职工岗位性质维护表 order by id
open gwxz
fetch from gwxz into @gwxz
WHILE @@FETCH_STATUS = 0
begin
select @sqlstr='select @a=count(*) from '+@tablename+' where '+
' type ='''+@gwlb+''' and postnature='''+@gwxz+''''
select @sqlstrI='select @a=count(*) from '+@tablename+' where '+
' type ='''+@gwlb+''''
select @sqlstrII='select @a=count(*) from '+@tablename+' where '+
' postnature ='''+@gwxz+''''
exec sp_executesql @sqlstr,N'@a int output',@rcount output
exec sp_executesql @sqlstrI,N'@a int output',@rtotalcount output
exec sp_executesql @sqlstrII,N'@a int output',@ctotalcount output
insert into #test
values(@rcount,@ctotalcount,@rtotalcount,@gwlb,@gwxz)
fetch next from gwxz into @gwxz
end
close gwxz
deallocate gwxz
fetch next from gwlb into @gwlb
end
CLOSE gwlb--关闭游标
DEALLOCATE gwlb--释放游标
select * from #test
end
end
按照
SELECT a.type, b.postnature, b.compcode
FROM 职工岗位管理情况表 AS b
INNER JOIN 在职职工基本信息表 AS a ON b.name = a.statusname
join 在职职工基本信息表用工类别维护表 c on a.type = c.name
join 职工岗位性质维护表 d on b.postnature = d.name
WHERE (a.move = '在岗') 建好的视图 名为 zgry
之前问的那个sql语句是为了在这个存储过程中调用的动态表或视图.问题I:select count(*),postnature from zgry group by postnature这个语句执行起来都费事.问题II：我不知道存储过程是否也能优化，不知道有什么问题，之前写的一个没执行很快，和这个存储过程差不多，就是动态传入的视图或表的数据量较大。希望能够给与支持。
