php - 根据单独的表插入 `COUNT(*)`

Question

在 MySQL 中，是否可以从一个表中选择列，同时还根据其他表为 COUNT(*) 创建一个列？这样可以返回所有表的结果摘要。用文字解释可能有点困惑，所以我制作了一些示例表格:

events_tbl
----------------------------
id      |   eventname
1       |   Anime Festival
2       |   Food Festival   
----------------------------

booths_tbl
-------------------------
id      |   boothname
1       |   Walmart
2       |   Pizza Hut
3       |   Nike
4       |   North Face
-------------------------

participants_tbl
-----------------------------
id      |   participantname
1       |   John
2       |   Mike
3       |   Rambo
4       |   Minnie
-----------------------------

event_booths_tbl
--------------------------------
event_id        |       booth_id
1               |       1
1               |       2
1               |       5
2               |       3
2               |       4
--------------------------------

event_participants_tbl
-------------------------------------
event_id        |       booth_id
1               |       1
1               |       2
1               |       3
1               |       4
-------------------------------------

有没有办法在 MySQL 中得到这样的结果:

summary_tbl
------------------------------------------------------------------------
id  |   eventname       |   booth_count     |       participant_count
1   |   Anime Festival  |   3               |       4
2   |   Food Festival   |   2               |       0
------------------------------------------------------------------------

Answer 1

event_participants_tbl 应包含 participant_id 而不是 booth_id。否则无关紧要。您的 MySQL 查询将是这样的:

select
 et.id,
 et.eventname,
 count(distinct ebt.booth_id) as booth_count,
 count(distinct ept.participant_id) as participant_count
from
 event_booths_tbl ebt
 left join events_tbl et on et.id=ebt.event_id
 left join event_participants_tbl ept on ept.event_id=ebt.event_id
group by et.event_id;