sql综合子表联查

最新推荐文章于 2023-06-09 11:26:33 发布

wushijun5200

最新推荐文章于 2023-06-09 11:26:33 发布

阅读量125

点赞数

分类专栏： net 文章标签： sql 数据库 mysql

本文链接：https://blog.csdn.net/wushijun5200/article/details/129581221

版权

net 专栏收录该内容

20 篇文章 1 订阅

订阅专栏

第一种求子表的和在主表显示

 SELECT s.gcid,s.xlname,s.xlbm,s.gcname,s.gcyear,s.gcmonth,s.gctypename,s.pfprice,(case s.paystate when  0  then  '未支付' when 1 then '部分' when  2  then  '结清' else  '' end) as paystate,s.gckgdate,s.gcwgdate,s.pfwh,s.htongprice,s.sgcompany,s.sguser,s.jlcompany,s.jluser,s.xlbm,s.xlname,s.zyprice,s.dfprice,(SELECT COALESCE( sum(CAST(c.gcnoeprice AS decimal(18,3))),0)  FROM tl_road_aagcgl_pay c WHERE c.gcid=s.gcid) as gcnoeprice ,(  s.htongprice-  (SELECT  COALESCE( sum(CAST(c.gcnoeprice AS decimal(18,3)))  ,0)   FROM tl_road_aagcgl_pay c WHERE c.gcid=s.gcid) )as gcnowwfprice FROM tl_road_aagcgl s    where  gcnowwfprice  !='0' and s.gcnoeprice !='0'

第二种，再对临时字段搜索处理

  select  *  from   (SELECT s.gcid,s.xlzh,s.gcbh,s.zzsave,s.gcyear,s.gcmonth,s.gctypename,s.gcname,(case s.paystate when  0  then  '未支付' when 1 then '部分' when  2  then  '结清' else  '' end) as paystate,s.sgcop,s.sguser,s.planprice,s.jsprice,s.bzprice,s.zfdate,s.kgdate,s.wgdate,s.xlbm,s.xlname,s.gcfjsm,(SELECT COALESCE( sum(CAST(c.gcnoeprice AS decimal(18,3))),0)   FROM tl_road_zxxx_xmgc_pay c WHERE c.gcid=s.gcid) as gcnoeprice ,( s.jsprice-  (SELECT  COALESCE( sum(CAST(c.gcnoeprice AS decimal(18,3)))  ,0)   FROM tl_road_zxxx_xmgc_pay c WHERE c.gcid=s.gcid) ) as gcnowwfprice FROM tl_road_zxxx_xmgc s   where  (country =  '" + country + "')   and del='1'  )  s   where  gcnowwfprice ='0' AND gcnoeprice !='0'