MySQL:当每个订单有多个账单表(以及多次付款)时，仅显示有余额的订单

Question

我的目标是获得一个(相对高效的)MySQL View ，显示所有仍有未结余额的订单——显示每个订单的发票金额、所有存储费用的总和、支付的总金额以及应付总额。这两个“账单”是订单表中的原始发票金额，然后是存储表中所有存储金额(费用)的总和。

订单表中有发票金额，付款表中有特定订单的所有付款，存储表中有特定订单的所有额外存储费用(付款表和存储表与订单表的order_id相关)字段)。

下面是我的架构和示例数据(这里是 SQL fiddle 如果有帮助):

CREATE TABLE orders
(
order_id int NOT NULL AUTO_INCREMENT,
invoice decimal(10,2),
PRIMARY KEY (order_id)
);


CREATE TABLE payments
(
payments_id int NOT NULL AUTO_INCREMENT,
order_id int,
amount decimal(10,2),
PRIMARY KEY (payments_id)
);

CREATE TABLE storage
(
storage_id int NOT NULL AUTO_INCREMENT,
order_id int,
amount decimal(10,2),
PRIMARY KEY (storage_id)
);


INSERT INTO orders (invoice)
VALUES ('250'), (NULL), ('150'), ('175.00'), ('175.00'), (NULL);

INSERT INTO payments (order_id, amount)
VALUES ('1', '50'), ('3', '50'), ('1', '100'), ('1', '150'), ('4', '100'), ('4', '25'), ('3', '50');

INSERT INTO storage (order_id, amount)
VALUES ('1', '50'), ('4', '25');

以下查询有效，但因为我重复了很多次，而且看起来总共有 5 个子查询，所以我猜这不是使其正常工作的有效方法:

CREATE VIEW accounts_receivable AS
SELECT
orders.order_id AS orderid,
orders.invoice,
(SELECT sum(amount) from storage WHERE storage.order_id = orders.order_id) AS storagebill,
coalesce(sum(payments.amount), 0) AS paid,
coalesce(orders.invoice, 0) + (SELECT coalesce(SUM(storage.amount), 0) FROM storage WHERE storage.order_id = orders.order_id) - (SELECT coalesce(sum(payments.amount), 0) FROM payments WHERE orders.order_id = payments.order_id) AS due
FROM orders
LEFT JOIN payments on payments.order_id = orders.order_id
WHERE
(coalesce(orders.invoice, 0) + (SELECT coalesce(SUM(storage.amount), 0) FROM storage WHERE storage.order_id = orders.order_id) - (SELECT coalesce(sum(payments.amount), 0) FROM payments WHERE orders.order_id = payments.order_id)) > 0
GROUP BY orders.order_id

有什么方法可以更有效地获取这些数据吗？我猜想这里有一些不同的方法来加入数据，但我已经尝试过(并寻找答案)，但没有成功。我对 SQL 比较陌生。

Answer 1

很抱歉重新格式化。我质疑存储表以及为什么发票不分成更小的部分。但是，我不知道详细信息，也许这是一件好事:)这是一个有效的查询，它可以为您提供类似的结果，但可读性更强。

SELECT
    * 
FROM
(
    SELECT 
        orderid,
        invoice,
        storage,
        totaldue=invoice+storage,
        totalpaid=paid,
        amountdue=(invoice+storage)-paid
    FROM
    (
            SELECT
                orderid=orders.order_id,
                invoice=orders.invoice,
                storage=
                (
                    SELECT SUM(amount) FROM storage where order_id=orders.order_id
                ),
                paid=SUM(payments.amount)   
            FROM
                orders
                LEFT OUTER JOIN payments on payments.order_id=orders.order_id                       
            GROUP BY
                orders.order_id,orders.invoice
    )AS DETAIL  
)AS SUMMARY
WHERE
    SUMMARY.amountdue>0