发表于 2015-3-10 11:07 | 来自
51CTO网页
引用:原帖由 UltraSQL 于 2015-3-9 23:48 发表
麻烦楼主把查询语句和执行计划贴出来!SELECT
row_number() over (order by t2.医生姓名) 序号
,t2.医生代码,t2.医生姓名,t2.总处方,t3.未点评,t4.已点评
,case when (t4.已点评>0 and t5.合格 IS null) then 0 else t5.合格 end 合格
,case when (t4.已点评>0 and t6.不合格 IS null) then 0 else t6.不合格 end 不合格
,cast(cast((case when (t4.已点评>0 and t5.合格 IS null) then 0 else t5.合格 end)*100.0/t4.已点评 as decimal(6,2)) as varchar(8))+'%' 合格率
,t7.抗菌药物处方,cast(cast(t7.抗菌药物处方*100.0/t2.总处方 as decimal(6,2)) as varchar(8))+'%' 抗菌药物使用率
,t8.抗菌药物静脉注射处方,cast(cast(t8.抗菌药物静脉注射处方*100.0/t2.总处方 as decimal(6,2)) as varchar(8))+'%' 抗菌药物静脉注射率
,t9.基药处方,cast(cast(t9.基药处方*100.0/t2.总处方 as decimal(6,2)) as varchar(8))+'%' 基药使用率
,t10.针剂处方,cast(cast(t10.针剂处方*100.0/t2.总处方 as decimal(6,2)) as varchar(8))+'%' 针剂使用率
,t11.总金额,cast(t11.总金额/t2.总处方 as decimal(8,2)) 平均处方金额
,t12.总用药种数,cast(t12.总用药种数*1.0/t2.总处方 as decimal(6,2)) 平均每人用药种数
FROM
(select YSDM 医生代码,YSNAME 医生姓名,COUNT(1) 总处方 from (select ysname,YSDM,blh from T_MZ_CFB A WHERE CFRQ between @Starttime and @endtime and exists( select * from T_SelectedPatient B where Back='门诊' and Delete_Bit=0 and time=@Starttime and A.BLH=B.Blh AND A.YSDM=B.ysdm) group by ysname,YSDM,BLH) t1 group by ysname,YSDM) t2
left join (select YSDM 医生代码,COUNT(1) 未点评 from (select ysname,YSDM,blh from T_MZ_CFB A WHERE CFRQ between @Starttime and @endtime and Flag=0 and exists( select * from T_SelectedPatient B where Back='门诊' and Delete_Bit=0 and time=@Starttime and A.BLH=B.Blh AND A.YSDM=B.ysdm) group by ysname,YSDM,BLH) t1 group by ysname,YSDM) t3
on t2.医生代码=t3.医生代码 left join (select YSDM 医生代码,COUNT(1) 已点评 from (select ysname,YSDM,blh from T_MZ_CFB A WHERE CFRQ between @Starttime and @endtime and Flag<>0 and exists( select * from T_SelectedPatient B where Back='门诊' and Delete_Bit=0 and time=@Starttime and A.BLH=B.Blh AND A.YSDM=B.ysdm) group by ysname,YSDM,BLH) t1 group by ysname,YSDM) t4
on t2.医生代码=t4.医生代码 left join (select YSDM 医生代码,COUNT(1) 合格 from (select ysname,YSDM,blh from T_MZ_CFB A WHERE CFRQ between @Starttime and @endtime and Flag=1 and exists( select * from T_SelectedPatient B where Back='门诊' and Delete_Bit=0 and time=@Starttime and A.BLH=B.Blh AND A.YSDM=B.ysdm) group by ysname,YSDM,BLH) t1 group by ysname,YSDM) t5
on t2.医生代码=t5.医生代码
left join (select YSDM 医生代码,COUNT(1) 不合格 from (select ysname,YSDM,blh from T_MZ_CFB A WHERE CFRQ between @Starttime and @endtime and Flag=2 and exists( select * from T_SelectedPatient B where Back='门诊' and Delete_Bit=0 and time=@Starttime and A.BLH=B.Blh AND A.YSDM=B.ysdm) group by ysname,YSDM,BLH) t1 group by ysname,YSDM) t6
on t2.医生代码=t6.医生代码
left join (select YSDM 医生代码,COUNT(1) 抗菌药物处方
from (select ysname,YSDM,blh
from T_MZ_CFB A WHERE CFRQ between @Starttime and @endtime and CFID IN (SELECT distinct CFID FROM T_MZ_CFBMX WHERE KSSDJID>0) and exists( select *from T_SelectedPatient B where Back='门诊' and Delete_Bit=0 and time=@Starttime and A.BLH=B.Blh AND A.YSDM=B.ysdm) group by ysname,YSDM,BLH) t1 group by ysname,YSDM) t7
on t2.医生代码=t7.医生代码
left join (select YSDM 医生代码,COUNT(1) 抗菌药物静脉注射处方
from (select ysname,YSDM,blh from T_MZ_CFB A WHERE CFRQ between @Starttime and @endtime and CFID IN (SELECT distinct CFID FROM T_MZ_CFBMX WHERE KSSDJID>0 and JMZS=1) and exists( select * from T_SelectedPatient B where Back='门诊' and Delete_Bit=0 and time=@Starttime and A.BLH=B.Blh AND A.YSDM=B.ysdm) group by ysname,YSDM,BLH) t1 group by ysname,YSDM) t8
on t2.医生代码=t8.医生代码
left join (select YSDM 医生代码,COUNT(1) 基药处方
from (select ysname,YSDM,blh from T_MZ_CFB A WHERE CFRQ between @Starttime and @endtime and CFID IN (SELECT distinct CFID FROM T_MZ_CFBMX WHERE JCYP=1) and exists( select * from T_SelectedPatient B where Back='门诊' and Delete_Bit=0 and time=@Starttime and A.BLH=B.Blh AND A.YSDM=B.ysdm) group by ysname,YSDM,BLH) t1 group by ysname,YSDM) t9
on t2.医生代码=t9.医生代码
left join (select YSDM 医生代码,COUNT(1) 针剂处方
from (select ysname,YSDM,blh from T_MZ_CFB A WHERE CFRQ between @Starttime and @endtime and CFID IN (SELECT distinct CFID FROM T_MZ_CFBMX WHERE ZSJYP=1) and exists( select * from T_SelectedPatient B where Back='门诊' and Delete_Bit=0 and time=@Starttime and A.BLH=B.Blh AND A.YSDM=B.ysdm) group by ysname,YSDM,BLH) t1 group by ysname,YSDM) t10
on t2.医生代码=t10.医生代码
left join (select YSDM 医生代码,ysname 医生姓名,SUM(zje) 总金额 from (select ysname,YSDM,blh,sum(ZJE) zje from T_MZ_CFB A WHERE CFRQ between @Starttime and @endtime and exists( select * from T_SelectedPatient B where Back='门诊' and Delete_Bit=0 and time=@Starttime and A.BLH=B.Blh AND A.YSDM=B.ysdm) group by ysname,YSDM,BLH) t1 group by YSDM,ysname) t11
on t2.医生代码=t11.医生代码
left join (select YSDM 医生代码 ,ysname 医生姓名,sum(num) 总用药种数 from (select A.YSDM,A.YSNAME,A.BLH,COUNT(DISTINCT B.XMID) num from T_MZ_CFB A INNER JOIN T_MZ_CFBMX B ON A.CFID=B.CFID where CFRQ between @Starttime and @endtime and exists( select * from T_SelectedPatient C where Back='门诊' and Delete_Bit=0 and time=@Starttime and A.BLH=C.Blh AND A.YSDM=C.ysdm) AND cfrq between @Starttime and @endtime GROUP BY A.BLH,A.YSDM,A.YSNAME) t1 group by YSDM,ysname) t12
on t2.医生代码=t12.医生代码