当出现空值时，从两个表中减去两个值 [英] subtract two values from two tables when null value eists

问题描述

我有3张桌子.
当我从null值中减去一个值时，结果为null，但这是不正确的.

为了更好的理解，请参见下图和查询

http://i46.tinypic.com/w1zkp4.jpg [

Hi i had 3 tables with joining.
when i am subtracting a value from null value, the result was null, but it is not correct.

for a better understanding see the below image and query

http://i46.tinypic.com/w1zkp4.jpg[^]

select p.partnerid,
sum(case when c.amount is not null then c.amount else 0 end) as amount,
sum(case when c.netamt is not null then c.netamt else 0 end) as total,
sum(case when (c.netamt - d.paidamount) is not null then (c.netamt - d.paidamount) else 0 end) as remainingamount,
sum(case when d.paidamount is not null then d.paidamount else 0 end) as paidamt
from customerinfo c
left join dailypayments d on c.accno = d.accno
right join partnerinfo p on c.partnerid = p.partnerid
where (d.paiddate is null or (d.paiddate >= '2011-3-15' and d.paiddate <= '2012-6-13')) and  p.manager = 7 group by p.partnerid

推荐答案

我认为您的代码中存在以下问题:

I believe the issue in your code is in this statement here:

case when (c.netamt - d.paidamount) is not null then (c.netamt - d.paidamount) else 0

当c.netamt或d.paidamount为零时，该值始终为零. Null不为零，因此您不能从中减去或与之相减.作为第一步，我建议如下:

This will always be zero when either c.netamt or d.paidamount is zero. Null is not zero, so you cannot subtract from it or with it. What I would recommend, as a first step, would be as follows:

COALESCE(c.netamt, 0) - COALESCE(d.paidamount,0)

最终代码如下所示:

The final code would look like this:

select p.partnerid,
sum(case when c.amount is not null then c.amount else 0 end) as amount,
sum(case when c.netamt is not null then c.netamt else 0 end) as total,
sum(case when (c.netamt - d.paidamount) is not null then (c.netamt - d.paidamount) else 0 end) as remainingamount,
sum(COALESCE(c.netamt, 0) - COALESCE(d.paidamount,0)) as paidamt
from customerinfo c
left join dailypayments d on c.accno = d.accno
right join partnerinfo p on c.partnerid = p.partnerid
where (d.paiddate is null or (d.paiddate >= '2011-3-15' and d.paiddate <= '2012-6-13')) and  p.manager = 7 group by p.partnerid

但是，我不喜欢在SUM语句中进行计算.我希望您执行一个SELECT语句，该语句获取每条记录并对其进行计算.然后，对该语句进行子查询，该语句对每列进行SUMS并按partnerid进行分组.那应该使事情变得更清晰，更高效.

另外，我使用了COALESCE语句而不是CASE语句. COALESCE返回第一个非空值.它比您做的要干净，并且MySQL支持它.它应该使您的生活更轻松.

However, I''m not a fan of doing calculations inside a SUM statement. I would rather see you do a SELECT statement that got every record and did the calculations on it. Then make that a sub-query of a statement that did the SUMS on each column and grouped by the partnerid. That should make things clearer and more performant.

Also, I used the COALESCE statement instead of the CASE statement. COALESCE returns the first non-null value. It is cleaner than what you are doing and MySQL supports it. It should make your life easier.

这篇关于当出现空值时，从两个表中减去两个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！