多个表的字段统计组合sql

WITH
DynamicData AS(select CaID,TableID,CySecID,CaName,CaRequireNumber,CaHoldDegree from CaseClass
left join Rec_CaseClass on Rec_CaseClass.FromID = CaseClass.CaID
where cysecid in (1518)
GROUP BY CaID,TableID,CySecID,CaName,CaRequireNumber,CaHoldDegree),
NoPassCheckDynamicData as(select CaID,count(CaID) AS NoPassCheckCount from CaseClass
left join Rec_CaseClass on Rec_CaseClass.FromID = CaseClass.CaID
where cysecid in (1518) AND userid = 4855 and (CheckStatus = 3 or CheckStatus =2)
GROUP BY CaID,TableID,CySecID,CaName,CaRequireNumber,CaHoldDegree),
NoCheckDynamicData as(select CaID,count(CaID) AS Finish from CaseClass
left join Rec_CaseClass on Rec_CaseClass.FromID = CaseClass.CaID
where cysecid in (1518) AND userid = 4855
GROUP BY CaID,TableID,CySecID,CaName,CaRequireNumber,CaHoldDegree),
CheckDynamicData as(select CaID,count(CaID) AS CheckCount from CaseClass
left join Rec_CaseClass on Rec_CaseClass.FromID = CaseClass.CaID
where cysecid in (1518) AND userid = 4855 and CheckStatus > 0
GROUP BY CaID,TableID,CySecID,CaName,CaRequireNumber,CaHoldDegree),
SSDynamicDataFinish as(select ExplanObjID,sum(Num) AS SSFinishCount from Explan
left join CaseClass on CaseClass.CaID = Explan.ExplanObjID
where Explan.cysecid in (1518) AND Explan.userid = 4855 and ExplanState = 1 AND ExplanSort=1
group by ExplanObjID),
SSDynamicData as(select ExplanObjID,sum(Num) AS SSCount from Explan
left join CaseClass on CaseClass.CaID = Explan.ExplanObjID
where Explan.cysecid in (1518) AND Explan.userid = 4855 AND ExplanSort=1
group by ExplanObjID)
SELECT DynamicData.CaID,TableID,CySecID,CaName,CaRequireNumber,CaHoldDegree,
isnull(Finish,0) as Finish,
isnull(CheckCount,0) as CheckCount,
isnull((Finish-CheckCount),0) AS Balance,
isnull(SSFinishCount,0) AS SSFinishCount,
isnull(SSCount,0) AS SSCount,
isnull(NoPassCheckCount,0) as NoPassCheckCount
FROM DynamicData
left join NoCheckDynamicData on NoCheckDynamicData.CaID = DynamicData.CaID
left join CheckDynamicData on CheckDynamicData.CaID = DynamicData.CaID
left join SSDynamicDataFinish on SSDynamicDataFinish.ExplanObjID = DynamicData.CaID
left join SSDynamicData on SSDynamicData.ExplanObjID = DynamicData.CaID
left join NoPassCheckDynamicData on NoPassCheckDynamicData.CaID=DynamicData.CaID
order by CaName