sql合并问题

悬赏园豆：5 [待解决问题]

 1 ----------------------------------麻醉效果评定统计表------------------------
 2 
 3 --新增加统计表，所有数据都从术后随访单上调用。
 4 ---------------麻醉效果评定统计表--------------------
 5 -- 项目名称 | 例数 |  项目名称    |例数|  项目名称  |例数| 项目名称 |例数|
 6 --麻醉并发症  2     术后镇痛并发症   15  不良反应    3     死亡  1
 7 --节肢瘫痪          运动障碍             异物遗留          猝死
 8 --所有症状          所有症状             所有症状          所有症状
 9 
10 --麻醉并发症集合 存储过程
11 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SP_MZXGPDTJ]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
12 -- 删除存储过程
13 drop procedure [dbo].[SP_MZXGPDTJ]
14 GO
15 create PROCEDURE [dbo].[SP_MZXGPDTJ]
16 AS
17 set NOCOUNT ON
18 
19 
20 DECLARE @51 TABLE (
21     MZBFZ varchar(20) ,     --麻醉并发症
22     MZBFZcount int ,    --麻醉并发症数量
23     SHBFZ varchar(20) ,     --术后镇痛并发症
24     SHBFZcount int     --术后镇痛并发症数量
25     
26     
27 )    
28 --------------------------------------------------------------------------------
29 insert  into @51(MZBFZ,MZBFZcount)
30  select bo.name MZBFZ,temp1.MZNum from bOpeDicyType bo
31 left join(
32 select  bdt.Name, COUNT(*) MZNum from dbo.fApplyAndDicyRole fdr,bOpeDicyType bdt,bOpeDictionary bo 
33 where fdr.bOpeDicyTypeID=bdt.ID and fdr.bOpeDicyID=bo.ID and bo.OpeDicyType_ID=bdt.ID and bdt.ID=2  group by bdt.Name)
34 as temp1 on temp1.Name=bo.Name  where bo.ID=2
35 union all
36 select bo.name,temp1.MZNum from bOpeDictionary bo
37 left join(
38 select  bo.Name, COUNT(*) MZNum from dbo.fApplyAndDicyRole fdr,bOpeDicyType bdt,bOpeDictionary bo 
39 where fdr.bOpeDicyTypeID=bdt.ID and fdr.bOpeDicyID=bo.ID and bo.OpeDicyType_ID=bdt.ID and bo.OpeDicyType_ID=2 group by bo.Name)
40 as temp1 on temp1.Name=bo.Name  where bo.OpeDicyType_ID=2
41 
42 insert  into @51(SHBFZ,SHBFZcount)
43 select bo.name SHBFZ,temp1.SHNum from bOpeDicyType bo
44 left join(
45 select  bdt.Name, COUNT(*) SHNum from dbo.fApplyAndDicyRole fdr,bOpeDicyType bdt,bOpeDictionary bo 
46 where fdr.bOpeDicyTypeID=bdt.ID and fdr.bOpeDicyID=bo.ID and bo.OpeDicyType_ID=bdt.ID and bdt.ID=15  group by bdt.Name)
47 as temp1 on temp1.Name=bo.Name  where bo.ID=15
48 union all
49 select bo.name,temp1.SHNum from bOpeDictionary bo
50 left join(
51 select  bo.Name, COUNT(*) SHNum from dbo.fApplyAndDicyRole fdr,bOpeDicyType bdt,bOpeDictionary bo 
52 where fdr.bOpeDicyTypeID=bdt.ID and fdr.bOpeDicyID=bo.ID and bo.OpeDicyType_ID=bdt.ID and bo.OpeDicyType_ID=15 group by bo.Name)
53 as temp1 on temp1.Name=bo.Name  where bo.OpeDicyType_ID=15
54 
55 --------------------------------------------------------------------------------
56 select MZBFZ,MZBFZcount,SHBFZ,SHBFZcount from @51