合并一条SQL语句 根据不同条件

参考例子：

SELECT TJJGMC,SUM(BYRS) BYRS,SUM(LJRS) LJRS,SUM(gxyBYRS) gxyBYRS,SUM(gxyLJRS) gxyLJRS,SUM(gxyGFRS) gxyGFRS,SUM(gxyGHLJRS) gxyGHLJRS
,SUM(tlbBYRS) tlbBYRS,SUM(tlbLJRS) tlbLJRS,SUM(tlbGFRS) tlbGFRS,SUM(tlbGHLJRS) tlbGHLJRS
,SUM(zxbBYRS) zxbBYRS,SUM(zxbLJRS) zxbLJRS,SUM(zxbGFRS) zxbGFRS,SUM(zxbGHLJRS) zxbGHLJRS
FROM
(select A.TJJGMC ,count(A.TJID) BYRS,0 LJRS,0 gxyBYRS,0 gxyLJRS,0 gxyGFRS,0 gxyGFLJRS
,0 tlbBYRS,0 tlbLJRS,0 tlbGFRS,0 tlbGHLJRS
,0 zxbBYRS,0 zxbLJRS,0 zxbGFRS,0 zxbGHLJRS FROM TB_JKTJ_LNRJKTJ A LEFT join zd_hospital B
on A.YLJGDM=B.orgcode and convert(varchar(7),A.TJRQ,20)=CONVERT(VARCHAR(7),GETDATE(),20)
GROUP BY A.TJJGMC
UNION ALL
select A.TJJGMC ,0 BYRS,count(A.TJID) LJRS ,0 gxyBYRS,0 gxyLJRS,0 gxyGFRS,0 gxyGHLJRS
,0 tlbBYRS,0 tlbLJRS,0 tlbGFRS,0 tlbGHLJRS
,0 zxbBYRS,0 zxbLJRS,0 zxbGFRS,0 zxbGHLJRS FROM TB_JKTJ_LNRJKTJ A LEFT join zd_hospital B on A.YLJGDM=B.orgcode GROUP BY A.TJJGMC
UNION ALL
--高血压本月人数--
select '' TJJGMC,0 BYRS,0 LJRS,COUNT(C.FID) gxyBYRS,0 gxyLJRS,0 gxyGFRS,0 gxyGHLJRS
,0 tlbBYRS,0 tlbLJRS,0 tlbGFRS,0 tlbGHLJRS
,0 zxbBYRS,0 zxbLJRS,0 zxbGFRS,0 zxbGHLJRS FROM TB_JBGL_GXYSQFZJL C LEFT join zd_hospital B on C.YLJGDM=B.orgcode WHERE convert(varchar(7),SBSJ,20)=CONVERT(VARCHAR(7),GETDATE(),20) GROUP BY C.YLJGDM
UNION ALL
--高血压累计人数--
select '' TJJGMC,0 BYRS,0 LJRS,0 gxyBYRS,COUNT(F.FID) gxyLJRS ,0 gxyGFRS,0 gxyLJRS,0 tlbBYRS,0 tlbLJRS,0 tlbGFRS,0 tlbGHLJRS
,0 zxbBYRS,0 zxbLJRS,0 zxbGFRS,0 zxbGHLJRS FROM TB_JBGL_GXYSQFZJL F LEFT join zd_hospital B on F.YLJGDM=B.orgcode GROUP BY F.YLJGDM
UNION ALL
--高血压本月规范化(一年内随访超四次)人数--
select '' TJJGMC,0 BYRS,0 LJRS,0 gxyBYRS,0 gxyLJRS ,COUNT(G.CID) gxyGFRS,0 gxyGHLJRS,0 tlbBYRS,0 tlbLJRS,0 tlbGFRS,0 tlbGHLJRS
,0 zxbBYRS,0 zxbLJRS,0 zxbGFRS,0 zxbGHLJRS FROM TB_JBGL_GXYSQBG G LEFT join zd_hospital B on G.YLJGDM=B.orgcode
WHERE JKDABZ IN(SELECT JKDABZ FROM TB_JBGL_GXYSQBG GROUP BY JKDABZ,JCCRQ HAVING COUNT(JKDABZ)>4)
and convert(varchar(7),G.JCCRQ,20)=CONVERT(VARCHAR(7),GETDATE(),20)
--高血压规范化累计人数--
UNION ALL
select '' TJJGMC,0 BYRS,0 LJRS,0 gxyBYRS,0 gxyLJRS ,0 gxyGFRS,COUNT(G.CID) gxyGHLJRS,0 tlbBYRS,0 tlbLJRS,0 tlbGFRS,0 tlbGHLJRS
,0 zxbBYRS,0 zxbLJRS,0 zxbGFRS,0 zxbGHLJRS FROM TB_JBGL_GXYSQBG G LEFT join zd_hospital B on G.YLJGDM=B.orgcode
WHERE JKDABZ IN(SELECT JKDABZ FROM TB_JBGL_GXYSQBG GROUP BY JKDABZ,JCCRQ HAVING COUNT(JKDABZ)>4)
--糖尿病本月管理人数--
UNION ALL
SELECT '' TJJGMC,0 BYRS,0 LJRS,0 gxyBYRS,0 gxyLJRS,0 gxyGFRS,0 gxyGHLJRS
,COUNT(BGKBH) tlbBYRS,0 tlbLJRS,0 tlbGFRS,0 tlbGHLJRS
,0 zxbBYRS,0 zxbLJRS,0 zxbGFRS,0 zxbGHLJRS FROM TB_JBGL_TNBSQBG C LEFT join zd_hospital B on C.YLJGDM=B.orgcode WHERE convert(varchar(7),JJCRQ,20)=CONVERT(VARCHAR(7),GETDATE(),20) GROUP BY C.YLJGDM
UNION ALL
SELECT '' TJJGMC,0 BYRS,0 LJRS,0 gxyBYRS,0 gxyLJRS,0 gxyGFRS,0 gxyGHLJRS
,0 tlbBYRS,COUNT(BGKBH) tlbLJRS,0 tlbGFRS,0 tlbGHLJRS
,0 zxbBYRS,0 zxbLJRS,0 zxbGFRS,0 zxbGHLJRS FROM TB_JBGL_TNBSQBG C LEFT join zd_hospital B on C.YLJGDM=B.orgcode GROUP BY C.YLJGDM
UNION ALL
SELECT '' TJJGMC,0 BYRS,0 LJRS,0 gxyBYRS,0 gxyLJRS,0 gxyGFRS,0 gxyGHLJRS
,0 tlbBYRS,0 tlbLJRS,COUNT(BGKBH) tlbGFRS,0 tlbGHLJRS
,0 zxbBYRS,0 zxbLJRS,0 zxbGFRS,0 zxbGHLJRS FROM TB_JBGL_TNBSQBG C LEFT join zd_hospital B on C.YLJGDM=B.orgcode
WHERE JKDABZ IN(SELECT JKDABZ FROM TB_JBGL_TNBSQBG GROUP BY JKDABZ,JJCRQ HAVING COUNT(JKDABZ)>4)
and convert(varchar(7),C.JJCRQ,20)=CONVERT(VARCHAR(7),GETDATE(),20)
UNION ALL
SELECT '' TJJGMC,0 BYRS,0 LJRS,0 gxyBYRS,0 gxyLJRS,0 gxyGFRS,0 gxyGHLJRS
,0 tlbBYRS,0 tlbLJRS,0 tlbGFRS,COUNT(BGKBH)tlbGHLJRS
,0 zxbBYRS,0 zxbLJRS,0 zxbGFRS,0 zxbGHLJRS FROM TB_JBGL_TNBSQBG C LEFT join zd_hospital B on C.YLJGDM=B.orgcode
WHERE JKDABZ IN(SELECT JKDABZ FROM TB_JBGL_TNBSQBG GROUP BY JKDABZ,JJCRQ HAVING COUNT(JKDABZ)>4)
--重性精神病患者本月--
UNION ALL
SELECT '' TJJGMC,0 BYRS,0 LJRS,0 gxyBYRS,0 gxyLJRS,0 gxyGFRS,0 gxyGHLJRS
,0 tlbBYRS,0 tlbLJRS,0 tlbGFRS,0 tlbGHLJRS
,COUNT(JSJBGLKID) zxbBYRS,0 zxbLJRS,0 zxbGFRS,0 zxbGHLJRS FROM TB_JBGL_JSBKFDJ C LEFT join zd_hospital B on C.YLJGDM=B.orgcode WHERE convert(varchar(7),SCQZRQ,20)=CONVERT(VARCHAR(7),GETDATE(),20) GROUP BY C.YLJGDM

--重性精神病患者管理人数--
UNION ALL
SELECT '' TJJGMC,0 BYRS,0 LJRS,0 gxyBYRS,0 gxyLJRS,0 gxyGFRS,0 gxyGHLJRS
,0 tlbBYRS,0 tlbLJRS,0 tlbGFRS,0 tlbGHLJRS
,0 zxbBYRS,COUNT(JSJBGLKID) zxbLJRS,0 zxbGFRS,0 zxbGHLJRS FROM TB_JBGL_JSBKFDJ C LEFT join zd_hospital B on C.YLJGDM=B.orgcode GROUP BY C.YLJGDM

--重性精神病患者规范化本月人数--
UNION ALL
SELECT '' TJJGMC,0 BYRS,0 LJRS,0 gxyBYRS,0 gxyLJRS,0 gxyGFRS,0 gxyGHLJRS
,0 tlbBYRS,0 tlbLJRS,0 tlbGFRS,0 tlbGHLJRS
,0 zxbBYRS,0 zxbLJRS,COUNT(JSJBGLKID) zxbGFRS,0 zxbGHLJRS FROM TB_JBGL_JSBKFDJ C LEFT join zd_hospital B on C.YLJGDM=B.orgcode
WHERE JKDABZ IN(SELECT JKDABZ FROM TB_JBGL_JSBKFDJ GROUP BY JKDABZ,SCQZRQ HAVING COUNT(JKDABZ)>4)
and convert(varchar(7),C.SCQZRQ,20)=CONVERT(VARCHAR(7),GETDATE(),20)
UNION ALL
SELECT '' TJJGMC,0 BYRS,0 LJRS,0 gxyBYRS,0 gxyLJRS,0 gxyGFRS,0 gxyGHLJRS
,0 tlbBYRS,0 tlbLJRS,0 tlbGFRS,0 tlbGHLJRS
,0 zxbBYRS,0 zxbLJRS,0 zxbGFRS,COUNT(JSJBGLKID) zxbGHLJRS FROM TB_JBGL_JSBKFDJ C LEFT join zd_hospital B on C.YLJGDM=B.orgcode
WHERE JKDABZ IN(SELECT JKDABZ FROM TB_JBGL_JSBKFDJ GROUP BY JKDABZ,SCQZRQ HAVING COUNT(JKDABZ)>4)
--重性精神病患者规范化累计人数--

)A
GROUP BY TJJGMC

-------稍微复杂点的SQL语句-----

case when  (A.XB='' or A.XB is null) then '_' else A.XB end as xbbm,C.CSRQ as csrq,case when datediff(year,C.CSRQ,getdate())<=3 then '0-3' when datediff(year,C.CSRQ,getdate())<=6 then '4-6' when datediff(year,C.CSRQ,getdate())<=12 then '7-12' when datediff(year,C.CSRQ,getdate())<=18 then '13-18' when datediff(year,C.CSRQ,getdate())<=24 then '19-24' when datediff(year,C.CSRQ,getdate())<=30 then '25-30' when datediff(year,C.CSRQ,getdate())<=40 then '31-40' when datediff(year,C.CSRQ,getdate())<=50 then '41-50' when datediff(year,C.CSRQ,getdate())<=60 then '51-60' when datediff(year,C.CSRQ,getdate())<=65 then '61-65' when datediff(year,C.CSRQ,getdate())>65 then '65up' else '0' end as nld from TB_JBGL_TNBSQBG A left join zd_hospital B on A.YLJGDM=B.orgcode left join TB_CHSS_GRJKDA C on A.JKDABZ=C.GRDAID and A.BZFLX=C.BZFLX)";