我认为你需要条件聚合。逻辑不是很明确（特别是对于 due 列），但我认为这应该接近您想要的：

select 
    p.description,
    coalesce(sum((i.time_paid is null and i.time_cancelled is null and i.time_refund is null) * ii.line_total_price)), 0) due,
    coalesce(sum((i.time_paid is not null) * ii.line_total_price)), 0) paid,
    coalesce(sum((i.time_cancelled is not null) * ii.line_total_price)), 0) cancelled,
    coalesce(sum((i.time_refunded is not null) * ii.line_total_price)), 0) refunded
from products p
left join invoice_item ii on ii.product_id = p.id
left join invoices i on i.id = ii.invoice_id
group by p.id, p.description

这会让你知道怎么做。您可能需要摆弄case语句内部的逻辑，以便它是正确的，但这只是一个开始：

DROP TABLE invoice_item;
DROP TABLE product;
DROP TABLE invoice;

CREATE TABLE product (
id int,
sku varchar(32),
product_name varchar(128),
product_description text,
current_price decimal(8,2),
quantity_in_stock int
);

CREATE TABLE invoice (
id int,
invoice_number varchar(255),
customer_id int,
user_account_id int, 
total_price decimal(8,2),
time_issued DATETIME,
time_due DATETIME,
time_paid DATETIME,
time_canceled DATETIME,
time_refunded DATETIME
);

CREATE TABLE invoice_item (
  id int,
  invoice_id int,
  product_id int,
  quantity int,
  price decimal(8,2),
  line_total_price decimal(8,2)
);

insert into product (id,sku,product_name,product_description,current_price,quantity_in_stock) VALUES
(1,10,'Product 1','Description for Product 1',65,122),
(2,11,'Product 2','Description for Product 2',98,51),
(3,12,'Product 3','Description for Product 3',45,34),
(4,13,'Product 4','Description for Product 4',25,393),
(5,14,'Product 5','Description for Product 5',95,74),
(6,15,'Product 6','Description for Product 6',45,40),
(7,16,'Product 7','Description for Product 7',62,154),
(8,17,'Product 8','Description for Product 8',80,0),
(9,18,'Product 9','Description for Product 9',20,281),
(10,19,'Product 10','Description for Product 10',170,0);

INSERT INTO invoice (id, invoice_number,customer_id,user_account_id, total_price,time_issued,time_due,time_paid) VALUES
(1,'800c8d2fc967fe',7,4,1436,'2019-07-20 15:05:07','2019-07-27 15:05:07','2019-07-25 21:24:12'),
(2,'b27502b9f81e9d',9,2,1000,'2019-07-20 15:07:11','2019-07-27 15:07:11','2019-07-20 15:10:32'),
(3,'6b6bcfd3dfcd9b',3,2,360,'2019-07-20 15:06:15','2019-07-27 15:06:15','2019-07-31 21:22:11');

INSERT INTO invoice (id, invoice_number,customer_id,user_account_id, total_price,time_issued,time_due) VALUES
(4,'2196vac9a0120ad',5,2,1675,'2019-07-20 15:06:34','2019-07-27 15:06:34');

INSERT INTO invoice (id, invoice_number,customer_id,user_account_id, total_price,time_issued,time_due,time_paid,time_canceled) VALUES
(5,'4440d698878af70',6,2,9500,'2019-07-20 15:06:42','2019-07-27 15:06:42',NULL,'2019-07-22 11:17:02');

INSERT INTO invoice (id, invoice_number,customer_id,user_account_id, total_price,time_issued,time_due,time_paid,time_canceled,time_refunded) VALUES
(6,'4320bcab8fc4322',4,2,150,'2019-07-20 15:08:15','2019-07-27 15:08:15','2019-07-27 13:42:45',NULL,'2019-07-27 14:11:20');

INSERT INTO invoice_item
(id,invoice_id,product_id,quantity,price,line_total_price) VALUES
(1,1,1,20,65,1300),
(2,1,7,2,62,136),
(3,1,5,10,100,1000),
(4,3,10,2,180,360),
(5,4,1,5,65,325),
(6,4,2,10,95,950),
(7,4,5,4,100,400),
(8,5,10,100,95,9500),
(9,6,4,6,25,150);

  SELECT 
    p.product_name,
    (COALESCE (SUM(CASE WHEN i.time_paid is null and i.time_canceled is null and i.time_refunded is null IS NOT NULL THEN it.quantity * it.price ELSE 0 END ),0)) as due,
    (COALESCE (SUM(CASE WHEN i.time_paid IS NOT NULL THEN it.quantity * it.price ELSE 0 END ),0)) as paid,
    (COALESCE (SUM(CASE WHEN i.time_canceled IS NOT NULL THEN it.quantity * it.price ELSE 0 END ),0)) as canceled,
    (COALESCE (SUM(CASE WHEN i.time_refunded IS NOT NULL THEN it.quantity * it.price ELSE 0 END ),0)) as refunded
FROM
    PRODUCT p
LEFT OUTER JOIN invoice_item it ON p.id = it.product_id
LEFT OUTER JOIN invoice i ON it.invoice_id = i.id
GROUP BY 
    product_name
ORDER BY 
    p.id ASC