mysql - 初学者 : How Can I make COUNT Function not be recurring with Join? MYSQL

Question

大家好，我是一名初级程序员，我一直在努力解决一个我认为是关于使用 COUNT 函数的简单问题。我有表格，我需要能够计算一个事件的可用门票数量。 requested_event 表具有事件容量。售票处有售票处。

SELECT a.BoughtTickets, b.event_capacity, b.event_id
FROM
(SELECT 
COUNT(*)  AS BoughtTickets 
FROM ticket
GROUP BY event_id

) a
JOIN
(SELECT 
event_capacity,
event_id
FROM requested_event JOIN approved_event
ON requested_event.reservation_id = approved_event.reservation_id
) b

我已经试过了，但它回来了

    Bought Tickets  event_capacity  event_id
3                   150             1
1                   150             1
3                   1500            2
1                   1500            2

实际上，event_id 1 仅售出 3 张门票，event_id 2 仅售出 1 张门票。如有任何帮助，我们将不胜感激。

Answer 1

您错过了 JOIN 条件。

SELECT a.BoughtTickets, b.event_capacity, b.event_id
FROM (SELECT event_id, COUNT(*)  AS BoughtTickets 
      FROM ticket
      GROUP BY event_id) a
JOIN (SELECT  event_capacity, event_id
      FROM requested_event 
      JOIN approved_event
        ON requested_event.reservation_id = approved_event.reservation_id ) b
 ON a.event_id = b.event_id  // here