mysql - sql查询以查找具有与会者人数的事件的所有 session

Question

我正在尝试获取一个事件的所有 session ，然后统计所有订阅该事件的与会者(session_attendee 表)。

我的架构

5 表

事件 -> 日 -> session

与会者

session_attendee

+------------------------------+
|           attendee           |
+------------------------------+
| id  username    password     |
| 1   user1       test         |
| 2   user2       test         |
| ------                       |
| event                        |
| id  name                     |
| 1   event 1                  |
| 2   event 2                  |
| 3   event 3                  |
| -----                        |
| day                          |
| id  date            event_id |
| 1   '2015-06-01'    1        |
| 2   '2015-06-02'    1        |
| 3   '2015-07-01'    2        |
| 4   '2015-07-02'    2        |
| ------                       |
| session                      |
| id  name        day_id       |
| 1   session a   1            |
| 2   session b   1            |
| 3   session c   2            |
| 4   session d   2            |
| ------                       |
| session_attendee             |
| id  session_id  attendee_id  |
| 1   1           1            |
| 2   2           1            |
| 3   1           2            |
| 4   2           2            |
+------------------------------+

我的期望

+--------------+-----------+
| Session Name | Attendees |
+--------------+-----------+
| session a    |         2 |
| session b    |         2 |
| session c    |         0 |
| session d    |         0 |
+--------------+-----------+

我的尝试(它可能有一些额外的列)

SELECT day.event_id, session.id, session.name,
(SELECT COUNT(*) FROM day WHERE day.event_id = event.id) AS days,
(SELECT COUNT(distinct attendee_id)) AS attendees
FROM event
LEFT JOIN day ON event.id = day.event_id
LEFT JOIN session ON day.id = session.day_id
LEFT JOIN session_attendee ON session.id = session_attendee.session_id
WHERE day.event_id = 1
ORDER BY day.date, session.start;

SQL fiddle

http://sqlfiddle.com/#!9/72ffc

编辑

伙计们，请检查一下，它有效，请提出任何意见。

SELECT event.id, day.date, session.name, session.day_id, session_attendee.id, session_attendee.session_id, session_attendee.attendee_id,
COUNT(distinct attendee_id) AS attendees,
(SELECT COUNT(*) FROM day WHERE day.event_id = 1) AS days
FROM session
RIGHT JOIN session_attendee ON session.id = session_attendee.session_id 
RIGHT JOIN day ON session.day_id = day.id
RIGHT JOIN event ON day.event_id = event.id
WHERE event_id = 1
GROUP BY session.name
ORDER BY day.date;

Answer 1

select s.name, count(a.id) from session left join session_attendee sa on s.id=sa.session_id left join attendee a on a.id = sa.attendee_id group by s.name