mysql - 三个表的总和(金额)返回 null

Question

我有下表

create table supplier_paid_details(
    id bigint(10) NOT NULL AUTO_INCREMENT,
    payment_mode varchar(20),
    payment_date date,
    add_date timestamp DEFAULT 0,
    status varchar(20),
    supp_payment_id bigint(10) NOT NULL,
    primary Key(id),
    CONSTRAINT fk_paid FOREIGN KEY (supp_payment_id)
    REFERENCES supplier_payment_details(id)
    );

create table supplier_paid_check(
id bigint(10) NOT NULL AUTO_INCREMENT,
check_no bigint(10) NOT NULL, 
dated date,
payable_at varchar(50),
paid_to_acc_no varchar(30),
paid_to_bank varchar(30),
paid_to_branch varchar(30),
spd_id bigint(10),
add_date timestamp DEFAULT 0,
amount float(10,2),
status varchar(20),
primary Key(id),
CONSTRAINT fk_ckeck FOREIGN KEY (spd_id)
REFERENCES supplier_payment_details(id)
);



create table supplier_paid_online(
id bigint(10) NOT NULL AUTO_INCREMENT,
pay_ref_no varchar(50),
paid_from_bank varchar(50),
from_acc_no varchar(50),
paid_to_acc_no varchar(30),
paid_to_bank varchar(30),
paid_to_branch varchar(30),
spd_id bigint(10),
add_date timestamp DEFAULT 0,
amount float(10,2),
status varchar(20),
primary Key(id),
CONSTRAINT fk_online FOREIGN KEY (spd_id)
REFERENCES supplier_paid_details(id)
);

create table supplier_paid_cash(
id bigint(10) NOT NULL AUTO_INCREMENT,
to_person varchar(40),
designation varchar(40),
receipt_no varchar(30),
spd_id bigint(10),
add_date timestamp DEFAULT 0,
amount float(10,2),
status varchar(20),
primary Key(id),
CONSTRAINT fk_cash FOREIGN KEY (spd_id)
REFERENCES supplier_paid_details(id)
);

在这里，我想对 supplier_paid_check、supplier_paid_cash、supplier_paid_online 的金额进行求和。

为此，我正在使用以下查询，但这显示为空。

 select sum(ca.amount+ch.amount+onl.amount) as amount from
supplier_paid_details as pd
left join supplier_paid_cash as ca
on pd.id=ca.spd_id
left join supplier_paid_check as ch
on pd.id=ch.spd_id
left join supplier_paid_online as onl
on pd.id=onl.spd_id
where pd.supp_payment_id=1;

Answer 1

我想您的三个表之一中至少有一行 amount 具有 NULL 值。这最终会导致 NULL 结果，因为 DBMS 无法确定如何将此值与其他值结合起来处理。但有一个解决办法:您可以告诉 DBMS 如何处理 NULL 值。使用IFNULL。

select
    sum(IFNULL(ca.amount, 0) + IFNULL(ch.amount, 0) + IFNULL(onl.amount, 0)) as amount 
from
    supplier_paid_details as pd left join supplier_paid_cash as ca on pd.id=ca.spd_id
    left join supplier_paid_check as ch on pd.id=ch.spd_id
    left join supplier_paid_online as onl on pd.id=onl.spd_id
where
    pd.supp_payment_id=1;

了解有关 IFNULL 的更多信息，地址:MySQL Docs .