sql中保留到小数点后两位以及非空判断赋值为零-CSDN博客

SELECT a.dzzmc, a.dzzdm, a.px, CONVERT(decimal(18, 2), ISNULL(b.sjpfzdf, 0) * 0.6 + (ISNULL(a.zddfzdf, 0) + ISNULL(b.zddfzdf, 0)) / 2 * 0.4) AS zzdf, CONVERT(decimal(18,
2), ISNULL(a.dzzjssjpf, 0) * 0.6 + (ISNULL(a.dzzjszddf, 0) + ISNULL(b.dzzjszddf, 0)) / 2 * 0.4) AS dzzjs, CONVERT(decimal(18, 2), ISNULL(a.dflzsjpf, 0)
* 0.6 + (ISNULL(a.dflzzddf, 0) + ISNULL(b.dflzzddf, 0)) / 2 * 0.4) AS Dflz, CONVERT(decimal(18, 2), ISNULL(a.xcgzsjpf, 0) * 0.6 + (ISNULL(a.xcgzzddf, 0)
+ ISNULL(b.xcgzzddf, 0)) / 2 * 0.4) AS Xcgz, CONVERT(decimal(18, 2), ISNULL(a.ghgzsjpf, 0) * 0.6 + (ISNULL(a.ghgzzddf, 0) + ISNULL(b.ghgzzddf, 0)) / 2 * 0.4) AS ghgz,
CONVERT(decimal(18, 2), ISNULL(a.gqtgzsjpf, 0) * 0.6 + (ISNULL(a.gqtgzzddf, 0) + ISNULL(b.gqtgzzddf, 0)) / 2 * 0.4) AS gqtgz, CONVERT(decimal(18, 2),
ISNULL(a.bmgzsjpf, 0) * 0.6 + (ISNULL(a.bmgzzddf, 0) + ISNULL(b.bmgzzddf, 0)) / 2 * 0.4) AS bmgz, CONVERT(decimal(18, 2), ISNULL(a.xfwdsjpf, 0)
* 0.6 + (ISNULL(a.xfwdzddf, 0) + ISNULL(b.xfwdzddf, 0)) / 2 * 0.4) AS xfwd, CONVERT(decimal(18, 2), ISNULL(a.zhzlsjpf, 0) * 0.6 + (ISNULL(a.zhzlzddf, 0)
+ ISNULL(b.zhzlzddf, 0)) / 2 * 0.4) AS zhzl
FROM (SELECT dbo.BranchGradeInf.branchgradeid, dbo.BranchGradeInf.dzzdm, dbo.BranchGradeInf.pfsj, dbo.BranchGradeInf.qywhzddf, dbo.BranchGradeInf.zddfzdf,
dbo.BranchGradeInf.xfwdzddf, dbo.BranchGradeInf.xfwdsjpf, dbo.BranchGradeInf.zhzlzddf, dbo.BranchGradeInf.zhzlsjpf, dbo.BranchGradeInf.dflzzddf,
dbo.BranchGradeInf.dflzsjpf, dbo.BranchGradeInf.dzzjszddf, dbo.BranchGradeInf.dzzjssjpf, dbo.BranchGradeInf.xcgzzddf, dbo.BranchGradeInf.xcgzsjpf,
dbo.BranchGradeInf.ghgzzddf, dbo.BranchGradeInf.ghgzsjpf, dbo.BranchGradeInf.gqtgzzddf, dbo.BranchGradeInf.gqtgzsjpf, dbo.BranchGradeInf.bmgzzddf,
dbo.BranchGradeInf.bmgzsjpf, dbo.BranchGradeInf.qywhsjpf, dbo.BranchGradeInf.sjpfzdf, dbo.BranchGradeInf.zpf, dbo.BranchGradeInf.ldpf,
dbo.BranchGradeInf.qzpydf, dbo.BranchGradeInf.rwzbwwckf, dbo.BranchGradeInf.aqhbsgkf, dbo.BranchGradeInf.ygwfwjkf, dbo.BranchGradeInf.jhsywdbkf,
dbo.BranchGradeInf.dwbwdkf, dbo.BranchGradeInf.zhzlwdbkf, dbo.BranchGradeInf.dxkfzkf, dbo.BranchGradeInf.cewcrwjlf, dbo.BranchGradeInf.zzdf,
dbo.BranchGradeInf.jd, dbo.PartyBranch.dzzmc, dbo.PartyBranch.zbjb, dbo.PartyBranch.zblx, dbo.PartyBranch.dzzmcjc, dbo.PartyBranch.px
FROM dbo.BranchGradeInf INNER JOIN
dbo.PartyBranch ON dbo.BranchGradeInf.dzzdm = dbo.PartyBranch.dzzdm
WHERE (dbo.BranchGradeInf.jd = 3)) AS a FULL OUTER JOIN
(SELECT BranchGradeInf_1.branchgradeid, BranchGradeInf_1.dzzdm, BranchGradeInf_1.pfsj, BranchGradeInf_1.qywhzddf, BranchGradeInf_1.zddfzdf,
BranchGradeInf_1.xfwdzddf, BranchGradeInf_1.xfwdsjpf, BranchGradeInf_1.zhzlzddf, BranchGradeInf_1.zhzlsjpf, BranchGradeInf_1.dflzzddf,
BranchGradeInf_1.dflzsjpf, BranchGradeInf_1.dzzjszddf, BranchGradeInf_1.dzzjssjpf, BranchGradeInf_1.xcgzzddf, BranchGradeInf_1.xcgzsjpf,
BranchGradeInf_1.ghgzzddf, BranchGradeInf_1.ghgzsjpf, BranchGradeInf_1.gqtgzzddf, BranchGradeInf_1.gqtgzsjpf, BranchGradeInf_1.bmgzzddf,
BranchGradeInf_1.bmgzsjpf, BranchGradeInf_1.qywhsjpf, BranchGradeInf_1.sjpfzdf, BranchGradeInf_1.zpf, BranchGradeInf_1.ldpf,
BranchGradeInf_1.qzpydf, BranchGradeInf_1.rwzbwwckf, BranchGradeInf_1.aqhbsgkf, BranchGradeInf_1.ygwfwjkf, BranchGradeInf_1.jhsywdbkf,
BranchGradeInf_1.dwbwdkf, BranchGradeInf_1.zhzlwdbkf, BranchGradeInf_1.dxkfzkf, BranchGradeInf_1.cewcrwjlf, BranchGradeInf_1.zzdf,
BranchGradeInf_1.jd, PartyBranch_1.dzzmc, PartyBranch_1.zbjb, PartyBranch_1.zblx, PartyBranch_1.dzzmcjc, PartyBranch_1.px
FROM dbo.BranchGradeInf AS BranchGradeInf_1 INNER JOIN
dbo.PartyBranch AS PartyBranch_1 ON BranchGradeInf_1.dzzdm = PartyBranch_1.dzzdm
WHERE (BranchGradeInf_1.jd = 4)) AS b ON a.dzzdm = b.dzzdm
WHERE (a.zbjb = 1)