关于SQL Server存储过程的问题 我写了一个存储过程,查询的时候,结果根据ID值显示的两个表,如图我现在想实现,把这两个表的内容直接在存储过程中给合并起来(AddDate想相同的),然后存储过程查询的结果只返回一个表,请问能实现吗? 两个查询结果表,再inner join 通过日期字段关联, [解决办法] 创建成两张虚拟表,然后用union all拼接 select * from #a union all select * from #b [解决办法]
ALTER PROCEDURE [dbo].[sq_GetInfo] -- Add the parameters for the stored procedure here @StartDate datetime,@EndDate datetime AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; SELECT distinct a.ProcessId,b.ProcessName into #T FROM G3_FeedBackBody a left join G0_Process b on a.ProcessId=b.ProcessId order by a.ProcessId
declare @ProcessId varchar(10) declare cur_SelectData cursor for select ProcessId from #t for read only open cur_SelectData fetch next from cur_SelectData into @ProcessId while @@fetch_status=0 begin select convert(varchar(100),Datelist,23) as AddDate, isnull(PNum,0) as Hnum, isnull(SNum,0) as Hsnum, isnull(SQnum,0) Hsqnum from (SELECT * FROM DateTable ( @StartDate,@EndDate)) d left join(SELECT convert(varchar(100),a.AddDate,23) as Date, sum(case when ProcessId=@ProcessId then FNum else 0 end) as PNum, sum(case when ProcessId=@ProcessId then a.fnum*(b.itemwidth+b.itemheight)*0.0005 else 0 end) as SNum, sum(case when ProcessId=@ProcessId then a.fnum*b.itemwidth*b.itemheight*0.000001 else 0 end) as SQnum FROM G3_FeedBackbody a left join G3_LevelShelfBody b on a.bodyid=b.bodyid where convert(varchar(100),a.AddDate,23) between @StartDate and @EndDate group by convert(varchar(100),a.AddDate,23) ) c on c.Date=d.Datelist fetch next from cur_SelectData into @ProcessId
end close cur_SelectData deallocate cur_SelectData