mysql - 从另一个表添加列，但不影响 count()

Question

我已经有一个包含多个 JOIN 的查询，简单的预订列表

SELECT reservation.reservation_id, customer.customer_id, customer.name, count(ordered_services.reservation_id) AS num_of_ordered_services
FROM reservations
JOIN customers ON reservations.customer_id = customer.customer_id
LEFT JOIN ordered_services ON reservations.reservation_id = ordered_services.reservation_id
GROUP BY reservation.reservation_id, customer.customer_id, customer.name
ORDER BY reservation.reservation_id

输出类似

reservation_id | customer_id | name | num_of_ordered_services
     1         |  1909091202 | John |          2 
     2         |  2512541508 | Jane |          3 

我想添加另一列包含有关付款的信息，但简单的 JOIN、LEFT JOIN 会干扰现有的 count() 列。喜欢

SELECT reservation.reservation_id, count(payments.reservation_id) AS num_of_payments
FROM reservations
LEFT JOIN payments ON reservations.reservation_id = payments.reservation_id
GROUP BY reservation.reservation_id
ORDER BY reservation.reservation_id

reservation_id | num_of_payments
     1         |       0 
     2         |       2

但两者都是单一的结果。如何实现？

PS: num_of_payments 不是必须的，我只需要知道某个预订的付款是否存在 (1, 0)。谢谢

---

tbl结构，没什么特别的:预订

reservation_id | customer_id |        added
     1         |  1909091202 | 2011-11-04 02:37:28
     2         |  2512541508 | 2011-11-04 14:27:01

顾客

customer_id | name | personal information columns ...
 1909091202 | John |       |      | 
 2512541508 | Jane |       |      |  
    ...     | ...  |       |      |

付款

payment_id | reservation_id | customer_id | total |        added
     1     |        2       |  1909091202 |   199 | 2011-11-04 02:37:28
     2     |        2       |  2512541508 |    50 | 2011-11-04 14:27:01

Answer 1

您可以为附加字段使用子选择。

SELECT reservation.reservation_id, customer.customer_id, customer.name, 
count(ordered_services.reservation_id) AS num_of_ordered_services,
(SELECT count(*) FROM payments WHERE reservation.reservation_id=payments.reservation_id) AS num_of_payments
FROM reservations
JOIN customers ON reservations.customer_id = customer.customer_id
LEFT JOIN ordered_services ON reservations.reservation_id = ordered_services.reservation_id
GROUP BY reservation.reservation_id, customer.customer_id, customer.name
ORDER BY reservation.reservation_id