Question

I have this query and it seems to work most of the time, but I am still getting NULLs with it when I know it should return something,

我首先请slect/code>。



(select sum(total) from orders
        where customer = 9
        and iscredit = 1
        and isdeleted = false and not id = 1560)


this peace of code gets me a total amount of orders for customer 9 and is not the current order, like a previous orders total...also notice that iscredit = 1 meaning that these are credit orders.

Next I want to add this query

(select sum(total) from orders
        where customer = 9
        and iscredit = 0
        and isdeleted = false and not id = 1560)


同样,但认证为0,不是信用额。

next I want to subtract what has been paid with this query

- (select ifnull(sum(p.amount), 0) from payment p
        inner join orders o
        on p.order = o.id
        where o.customer = 9) as previous


因此,我要问的是......。

select 
(
    select sum(total) 
    from orders
    where customer = 9 and iscredit = 1 and isdeleted = false and not id = 1560
) 
+ 
(
    select sum(total) 
    from orders
    where customer = 9 and iscredit = 0 and isdeleted = false and not id = 1560
) 
- 
(
    select ifnull(sum(p.amount), 0) 
    from payment p
        inner join orders o
            on p.order = o.id
    where o.customer = 9
) as previous 


是否有更好的办法来写这句话?

任何建议都值得赞赏。

成就

Answer 1

与此类似,头两个总数将加在一起。

(select sum(total)
from orders
where customer = 0
and (iscredit = 1 or iscredit = 0)
and isdeleted = false
and not id = 1560)

如果没有对等关系,这种结果将无效。

(select ifnull(sum(total), 0)
from orders
where customer = 0
and (iscredit = 1 or iscredit = 0)
and isdeleted = false
and not id = 1560)

This is probably the source of your problems.

友情链接