行之间的复杂计算过程 其中一行可能为空

Question

>我在行上有一个复杂的计算过程我想做两个字段的乘法 （val1），如果我的条件等于 true，那么再做一次另外两个字段的乘法（Val 2）然后我计算总值的百分比（（val1 + val2）* 10%）/100问题是任何公寓可能等于假，然后结果等于空这是我的代码

SELECT DISTINCT
      r.paymentDate as "Payment Date" ,
      r.recivedID as "Recived ID" ,
      f.farmerName as "Farmer Name" ,   
      ra.tips as "percentage ",     

          ( ( (SELECT SUM(od.price * od.kilo) FROM orderDetailsTBL od WHERE od.recivedID = r. recivedID AND r.status = @STATUS AND od.calcType = 'k') +
            (SELECT SUM(od.quant * od.price) FROM orderDetailsTBL od WHERE od.recivedID = r. recivedID AND r.status = @STATUS AND od.calcType = 'n')  ) * ra.tips) / 100
                 as [amount]
FROM recivedTBL r
JOIN farmerTBL f ON f.farmerID = r.farmerID
JOIN recivedAccTBL ra ON ra.recivedID = r.recivedID
JOIN orderDetailsTBL od ON od.recivedID = r.recivedID
WHERE (r.paymentDate BETWEEN @D1 AND @D2) AND r.status = @STATUS

如果任何子查询条件等于 false，则此查询不返回值对于考试：

(SELECT SUM(od.quant * od.price) FROM orderDetailsTBL od WHERE od.recivedID = r. recivedID AND r.status = @STATUS AND od.calcType = 'n')  ) * ra.tips) / 100

如果此子查询等于 false，则返回值将为 null

Answer 1

这是你想要的吗？

SELECT r.paymentDate as "Payment Date", r.recivedID as "Recived ID",
       f.farmerName as "Farmer Name", ra.tips as "percentage",     
       SUM((CASE WHEN od.calcType = 'k' THEN od.price * od.kilo
                 WHEN od.calcType = 'n' THEN od.quant * od.price
            END) * ra.tips) / 100 as [amount]
FROM recivedTBL r JOIN
     farmerTBL f
     ON f.farmerID = r.farmerID JOIN
     recivedAccTBL ra
     ON ra.recivedID = r.recivedID JOIN
     orderDetailsTBL od
     ON od.recivedID = r.recivedID
WHERE r.paymentDate BETWEEN @D1 AND @D2 AND r.status = @STATUS
GROUP BY r.paymentDate, r.recivedID, f.farmerName, ra.tips;

Answer 2

将"金额"的整个计算包装在：

COALESCE(<your-calc>, 0) as [amount]

如果您的计算返回 NULL 将返回零