22,207
社区成员
发帖
与我相关
我的任务
分享
SELECT
c.id hdid ,
b.bscwd,
b.sydw,
c.hdbh,
c.fysj,
c.cys,
GROUP_CONCAT(DISTINCT b.qsd) qsd,
GROUP_CONCAT(DISTINCT b.mdd) mdd,
c.cph,
c.qszt,
round(sum(w.wjtj),2) ztj,
round(sum(ifnull(s.zyywsr,0)),2) zyywsr,
round(sum(ifnull(e.srbk,0)),2) srbk,
round(sum(ifnull(f.yj,0)),2) zyj,
round(sum(ifnull(f.zyywcb,0)),2) zyywcb,
round(sum(ifnull(d.srkk,0)),2) cbkk ,
round(sum(ifnull(s.zyywsr,0))+sum(ifnull(e.srbk,0))-sum(ifnull(f.zyywcb,0))-sum(ifnull(d.srkk,0)),2) lr,
s.srjszt srsfjs,
f.cbjszt cbsfjs
FROM
zhuangche b
LEFT JOIN lirun a ON a.zcid = b.id
LEFT JOIN huidan c ON c.pcid = b.pcId
LEFT JOIN chengbenzhuijia d ON b.id = d.zcid
LEFT JOIN shouruzhuijia e ON b.id = e.zcid
LEFT JOIN chengbens f ON b.id = f.zcid
LEFT JOIN shouru s on b.id = s.zcid
LEFT JOIN (select sum(ifnull(sstj,sftj)) wjtj,zcid from wujian group by zcid) w on w.zcid = b.id
WHERE 1=1
select format(zyywsr,2) as zyywsr ,format(ifnull(srbk,0),2) as srbk,format(zyywcb,2) as zyywcb ,format(ifnull(cbkk,0),2) as cbkk,format((round(zyywsr,2) + round(ifnull(srbk,0),2)-round(zyywcb,2) -round(ifnull(cbkk,0),2)),2) as lr from (
SELECT sum(s.zyywsr) zyywsr,
sum(e.srbk) srbk,
sum(f.zyywcb) zyywcb,
sum(d.srkk) cbkk
FROM
zhuangche b
LEFT JOIN lirun a ON a.zcid = b.id
LEFT JOIN huidan c ON c.pcid = b.pcId
LEFT JOIN chengbenzhuijia d ON b.id = d.zcid
LEFT JOIN shouruzhuijia e ON b.id = e.zcid
LEFT JOIN chengbens f ON b.id = f.zcid
LEFT JOIN shouru s on b.id = s.zcid
where c.hdbh IS NOT NULL
) a