1.增加临时的子表的求和列，-可多个求和
c子表tl_road_zxxx_xmgc_pay 也是要求和的表 gcnoeprice ，gcnowwfprice 两列求和 放在总表中显示;通过主gcid连接
总表 tl_road_zxxx_xmgc s 临时字段是
SELECT s.gcid,s.xlzh,s.gcbh,s.zzsave,s.gcyear,s.gcmonth,s.gctypename,s.gcname,s.sgcop,s.sguser,s.planprice,s.jsprice,s.bzprice,s.zfdate,s.kgdate,s.wgdate,s.xlbm,s.xlname,s.gcfjsm,(SELECT sum(CAST(c.gcnoeprice AS decimal(18,3))) FROM tl_road_zxxx_xmgc_pay c WHERE c.gcid=s.gcid) as gcnoeprice ,(SELECT sum(CAST(c.gcnowwfprice AS decimal(18,3))) FROM tl_road_zxxx_xmgc_pay c WHERE c.gcid=s.gcid) as gcnowwfprice FROM tl_road_zxxx_xmgc s
SELECT s.gcid,s.xlzh,s.gcbh,s.zzsave,s.gcyear,s.gcmonth,s.gctypename,s.gcname,s.sgcop,s.sguser,s.planprice,s.jsprice,s.bzprice,s.zfdate,s.kgdate,s.wgdate,s.xlbm,s.xlname,s.gcfjsm,(SELECT sum(CAST(c.gcnoeprice AS decimal(18,3))) FROM tl_road_zxxx_xmgc_pay c WHERE c.gcid=s.gcid) as gcnoeprice FROM tl_road_zxxx_xmgc s where (country = '522701') and del='1'
因为临时的字段不能参加where，所以做成整体
1. select * from ( 临时查询语句 ) s where gcnoeprice='0' //gcnoeprice为临时
2.作为整体的语句
SELECT s.gcid,s.xlzh,s.gcbh,s.zzsave,s.gcyear,s.gcmonth,s.gctypename,s.gcname,s.sgcop,s.sguser,s.planprice,s.jsprice,s.bzprice,s.zfdate,s.kgdate,s.wgdate,s.xlbm,s.xlname,s.gcfjsm,(SELECT sum(CAST(c.gcnoeprice AS decimal(18,3))) FROM tl_road_zxxx_xmgc_pay c WHERE c.gcid=s.gcid) as gcnoeprice ,(SELECT sum(CAST(c.gcnoeprice AS decimal(18,3)))-s.jsprice FROM tl_road_zxxx_xmgc_pay c WHERE c.gcid=s.gcid) as gcnowwfprice FROM tl_road_zxxx_xmgc s where (country = '522701') and del='1'
3.结合语句
select * from (SELECT s.gcid,s.xlzh,s.gcbh,s.zzsave,s.gcyear,s.gcmonth,s.gctypename,s.gcname,s.sgcop,s.sguser,s.planprice,s.jsprice,s.bzprice,s.zfdate,s.kgdate,s.wgdate,s.xlbm,s.xlname,s.gcfjsm,(SELECT sum(CAST(c.gcnoeprice AS decimal(18,3))) FROM tl_road_zxxx_xmgc_pay c WHERE c.gcid=s.gcid) as gcnoeprice ,(SELECT sum(CAST(c.gcnoeprice AS decimal(18,3)))-s.jsprice FROM tl_road_zxxx_xmgc_pay c WHERE c.gcid=s.gcid) as gcnowwfprice FROM tl_road_zxxx_xmgc s where (country = '522701') and del='1' ) s where gcnoeprice='0'
本文链接：https://my.lmcjl.com/post/14961.html
4 评论