sql子表求和在总表中显示，并参与搜索

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'