@我忒忙: 你把查询放出来我看看

@刘宏玺: 

select b.CPP_C_CaseNO as 案件号,c.policyno as 保单号,
b.CPP_C_Name as 姓名,b.CPP_C_IDNo as 证件号,
(select sum(z.CPP_B_Total) shenqing from C_CPP_BillInfo z where z.CPP_B_CaseNO=a.CPP_F_CaseNO) as zong,
(select sum(convert(float,w.CPP_B_Exto28)) yibao from C_CPP_BillInfo w where w.CPP_B_CaseNO=a.CPP_F_CaseNO) as 医保,
zong.shenqing as 审核未通过,
(select sum(t.calcute_amnt) from C_CL_BILLSPILTBAK t where t.caseparty_no=a.CPP_F_CaseNO) as B赔付
from C_CPP_FeeItemInfo a
left join C_CPP_CASEINFO b on a.CPP_F_CaseNO=b.CPP_C_CaseNO
left join C_CL_INSURED c on b.CPP_C_Exto9=c.InsuredNo
left join C_CPP_BillInfo d on a.CPP_F_CaseNO=d.CPP_B_CaseNO and a.CPP_F_BIllReceiptNo=d.CPP_b_BIllReceiptNo
left join C_CL_BILLSPILTBAK f on a.CPP_F_CaseNO=f.caseparty_no and a.CPP_F_BIllReceiptNo=f.bill_no
where (b.TransferNumber like '420801%' or b.TransferNumber like '430801%' ) and f.contract_no in ('42150407001',
'42150407002')
group by c.policyno,b.CPP_C_Name,b.CPP_C_IDNo,b.CPP_C_CaseNO,CPP_F_CaseNO

@刘宏玺: 

这是我全部的查询语句。 现在审核未通过那个字段 我想用 总金额的那个子查询 中的SUM 减去别的金额

@我忒忙: 

select b.CPP_C_CaseNO as 案件号,c.policyno as 保单号,
b.CPP_C_Name as 姓名,b.CPP_C_IDNo as 证件号,
(select sum(z.CPP_B_Total) shenqing from C_CPP_BillInfo z where z.CPP_B_CaseNO=a.CPP_F_CaseNO) as zong,
(select sum(convert(float,w.CPP_B_Exto28)) yibao from C_CPP_BillInfo w where w.CPP_B_CaseNO=a.CPP_F_CaseNO) as 医保,
(select sum(z.CPP_B_Total) shenqing from C_CPP_BillInfo z where z.CPP_B_CaseNO=a.CPP_F_CaseNO) + 1 as 审核未通过,
(select sum(t.calcute_amnt) from C_CL_BILLSPILTBAK t where t.caseparty_no=a.CPP_F_CaseNO) as B赔付
from C_CPP_FeeItemInfo a 
left join C_CPP_CASEINFO b on a.CPP_F_CaseNO=b.CPP_C_CaseNO
left join C_CL_INSURED c on b.CPP_C_Exto9=c.InsuredNo
left join C_CPP_BillInfo d on a.CPP_F_CaseNO=d.CPP_B_CaseNO and a.CPP_F_BIllReceiptNo=d.CPP_b_BIllReceiptNo
left join C_CL_BILLSPILTBAK f on a.CPP_F_CaseNO=f.caseparty_no and a.CPP_F_BIllReceiptNo=f.bill_no
where (b.TransferNumber like '420801%' or b.TransferNumber like '430801%' ) and f.contract_no in ('42150407001',
'42150407002')
group by c.policyno,b.CPP_C_Name,b.CPP_C_IDNo,b.CPP_C_CaseNO,CPP_F_CaseNO

@刘宏玺: 

这个语句是说只能把原来的子查询全部拿出来 然后计算吗

@我忒忙: 这种子查询是的

不过你想一个查询的话需要是用join来实现

@刘宏玺: 如何实现呢，大神

@我忒忙: 

select b.CPP_C_CaseNO as 案件号,c.policyno as 保单号,
b.CPP_C_Name as 姓名,b.CPP_C_IDNo as 证件号,
xxx.shenqing as zong,
(select sum(convert(float,w.CPP_B_Exto28)) yibao from C_CPP_BillInfo w where w.CPP_B_CaseNO=a.CPP_F_CaseNO) as 医保,
xxx.shenqing + 1 as 审核未通过,
(select sum(t.calcute_amnt) from C_CL_BILLSPILTBAK t where t.caseparty_no=a.CPP_F_CaseNO) as B赔付
from C_CPP_FeeItemInfo a 
left join C_CPP_CASEINFO b on a.CPP_F_CaseNO=b.CPP_C_CaseNO

left join (select z.CPP_B_CaseNO,sum(z.CPP_B_Total) shenqing from C_CPP_BillInfo z group by z.CPP_B_CaseNO) xxx on a.CPP_F_CaseNO = xxx.CPP_B_CaseNO

left join C_CL_INSURED c on b.CPP_C_Exto9=c.InsuredNo
left join C_CPP_BillInfo d on a.CPP_F_CaseNO=d.CPP_B_CaseNO and a.CPP_F_BIllReceiptNo=d.CPP_b_BIllReceiptNo
left join C_CL_BILLSPILTBAK f on a.CPP_F_CaseNO=f.caseparty_no and a.CPP_F_BIllReceiptNo=f.bill_no
where (b.TransferNumber like '420801%' or b.TransferNumber like '430801%' ) and f.contract_no in ('42150407001',
'42150407002')
group by c.policyno,b.CPP_C_Name,b.CPP_C_IDNo,b.CPP_C_CaseNO,CPP_F_CaseNO

@刘宏玺: 楼上的说法我不知道怎么操作了，，可以说一下嘛？

@我忒忙: 哪个是楼上？

@刘宏玺: 

@我忒忙: 其实他说的和你的问题有点答非所问

@刘宏玺: 怎么讲，求指点

@我忒忙: 他说的有点像我最后面写的带join的那个查询

但是你问的是查询字段的子查询

@刘宏玺: 嗯嗯，谢谢，我先试试，等会不懂来再来麻烦你，感谢大神

@刘宏玺: (*@ο@*) 哇～ 赞