主题：查询外部数据源数据问题

ALTER proc [dbo].[Plato_jxydjy](@i int)
as
begin
set nocount on
--设置过去的时间段
declare @dd datetime
select @dd=DATEADD(DAY,@i,GETDATE())
declare @prdname varchar(50)
set @prdname='8VT曲轴'
--找出检基
select rowid=identity(int,1,1),flag=0,project into #tb_jxydjy from MC_TF_JXYDJY tf where tf.记录号 in
(select top 1 MAIN_ID from MC_MF_JXYDJY where prdname=@prdname and  T_JYSJ>=@dd and T_JYSJ<=GETDATE())
--保存查询出来的结果

create table #tb_jxydjy_resoult
(
main_id varchar(50),prdname varchar(50),ysh float,bhgs float,fla1 float
)
--循环
declare @project varchar(20)
declare @rowid int
select @rowid=MIN(rowid) from #tb_jxydjy where flag=0
--保存查询出来的结果
declare @ysh float--验收数
declare @bhgs float--不合格数
while @rowid is not null
begin
 --检基
 select @project=PROJECT  from #tb_jxydjy where rowid=@rowid
 --进行查找结果，然后把结果存放到结果表中
 select @ysh=SUM(num.num_ys),@bhgs=SUM(num.num_bhg)
 from MC_MF_JXYDJY mf
 left join num_jxydjy num
 on mf.MAIN_ID=num.main_id
 where prdname=@prdname and  T_JYSJ>=@dd and T_JYSJ<=GETDATE()
 and LOST like '%'+@project+'不合格%'
 insert into #tb_jxydjy_resoult(MAIN_ID,prdname,ysh,bhgs) values (@project,@prdname,@ysh,@bhgs)
 update #tb_jxydjy set flag=1 where rowid=@rowid
 select @rowid=min(rowid) from #tb_jxydjy where flag=0
end
--查找结果中的集合
declare @sumlost float
select @sumlost=SUM(bhgs) from #tb_jxydjy_resoult
update #tb_jxydjy_resoult set fla1=@sumlost
declare @sql NVARCHAR(MAX)
--select main_id as 检基,prdname as 产品,ysh as 验收数量,bhgs as 不合格数, ((sum(bhgs)*1.00)/(sum(fla1)*1.00)*100) as 不合格率 into tbbbbb_mc_jxydjy from #tb_jxydjy_resoult group by main_id,prdname,ysh,bhgs
--order by bhgs desc
--set @sql='select main_id as 检基,prdname as 产品,ysh as 验收数量,bhgs as 不合格数 from #tb_jxydjy_resoultorder by bhgs desc'
--EXEC sp_executesql @sql
--清空建立的表
set nocount off
select * from #tb_jxydjy_resoult
drop table #tb_jxydjy_resoult
drop table #tb_jxydjy
end