求一条SQL 中的语句

[shanda3246]

select b.csfzh,a.CXM,case when CYBLB='11' then '在职' when CYBLB='21' then '退休' when CYBLB='31' then '离休' when CYBLB='32' then '老红军' when CYBLB='31' then '医保优诊' when CYBLB='33' then '二乙' when CYBLB='22' then '离休' when CYBLB IN('41') then '居民' when CYBLB='50' then '农民工'when CYBLB='61' then '师级以下军休'when CYBLB='62' then '师级及以上军休' else'其他' end CYBLB ,CZDMC,MZJE,MFY13,MXJZF,MFY3 MTCZF,MZHZF,MFY4,MFY8+MFY23 MZFFY,MFY9+MFY10+MFY11 MGRZF,CONVERT(varchar(10),a.DRYSJ,120) DRYSJ ,CONVERT(varchar(10),DJSSJ,120) DJSSJ from ZZSYBJK..TBZYJSXX a,(select * from yxhis2017..tbsyxx2017 where substring(CSFZH,14,1)in(8,9) union all select * from yxhis2018..tbsyxx2018 where substring(CSFZH,14,1)in(8,9) ) b WHERE a.czyh=b.czyh and ITFBZ=0 AND CYBLB IN('11','21') and cyllb not in('42','43','44') AND CBZ7='410199' and DJSSJ >= '2018-01-01' and DJSSJ < '2019-01-01' AND CCZYGH IN (SELECT CGH FROM TBCZY A,TBSYSCZY B WHERE A.CSRM=B.CUID AND CGH LIKE'01%' AND CJS IN(SELECT CBM FROM TBSYSJS WHERE CMC LIKE'%老院%' AND CXT LIKE'%01%')) order by DJSSJ




select CXM,case when CYBLB='11' then '在职' when CYBLB='21' then '退休' when CYBLB='32' then '老红军' when CYBLB='31' then '医保优诊' when CYBLB='33' then '二乙' when CYBLB='22' then '离休' when CYBLB IN('41') then '居民' end CYBLB ,CZDMC,MZJE,MFY20,MXJZF,MFY3 MTCZF,MZHZF,MFY4,case when cyllb<>'16' then '门诊规定病种' end DRYSJ ,CONVERT(varchar(10),DJSSJ,120) DJSSJ,substring(cbz3,11,18)csfzh from ZZSYBJK..TBMZJSXX WHERE ITFBZ=0 and cksmc like'%人民路%' AND substring(cbz3,24,1)in(8,9) AND CYBLB IN('11','21') and cyllb not in('42','43','44') AND CBZ8='410199' and DJSSJ >= '2018-01-01' and DJSSJ < '2019-01-01' order by DJSSJ



如何计算 这两个表中 相同身份证号码的MTCZF 相加大于5000

[p47655255]

select * from （.....） a, --第一个结果集
（.....） b --第二个结果集
where a.sfz= b.sfz and a.MTCZF + b.MTCZF >5000

[八月十八]

select a,b,c,sum(MTCZF) from
(select a,b,c,MTCZF(要显示的字段) from tabl1
nuion
select a,b,c,MTCZF(要显示的字段) from tabl2)
where MTCZF>5000
group by a,b,c
注意两表显示的字段需要一致

[八月十八]

select a,b,c,sum(MTCZF) from
(select a,b,c,MTCZF(要显示的字段) from tabl1
nuion
select a,b,c,MTCZF(要显示的字段) from tabl2) a
where MTCZF>5000
group by a,b,c
注意两表显示的字段需要一致

[kkwrite]

with ct as
(
--你上面的第一条查询
)

with cte as
(
--你上面的第二条查询
)

select * from ct a join (select csfzh,SUM(MTCZF) from cte group by csfzh) b on a.csfzh=b.csfzh and a.MTCZF+b.MTCZF>5000