mysql - 查询实体的余额到期日和到期日

Question

我正在尝试获取尚未全额支付总费用(仍有余额)的客人名单。

我想这样显示:

1231231(reservation id) - John Doe(guest name) 2013-11-17(date due) 153.14(balance due)

当然没有 ( )。

我进行了修补，但仍然找不到令人满意的查询。

这是 SQLFiddle 的链接.

如果我没有提供足够的信息，请告诉我。

Answer 1

你在找这样的东西吗？

SELECT r.id, 
       CONCAT(g.fname, ' ', g.lname) guest_name, 
       r.arrival date_due,
       r.total_price - COALESCE(p.payment_amt, 0) balance_due
  FROM reservations r JOIN guests g
    ON r.guest = g.id LEFT JOIN
(
  SELECT reservation, SUM(payment_amt) payment_amt
    FROM payments
   GROUP BY reservation
) p
    ON r.id = p.reservation
HAVING balance_due > 0

示例输出(基于提供的示例数据):

|      ID | GUEST_NAME |                        DATE_DUE | BALANCE_DUE ||---------|------------|---------------------------------|-------------|| 1000023 |   John Doe | November, 09 2013 00:00:00+0000 |        40.5 || 1000022 |   John Doe | November, 09 2013 00:00:00+0000 |        40.5 || 1000018 |   John Doe | November, 01 2013 00:00:00+0000 |          54 || 1000019 |   John Doe | November, 08 2013 00:00:00+0000 |          54 || 1000020 |  Mary Jane | November, 08 2013 00:00:00+0000 |          54 || 1000021 |  Mary Jane | November, 08 2013 00:00:00+0000 |          54 |

这是 SQLFiddle 演示