时间戳

首页 > 解决方案 > 使用 GROUP BY 的 SQL JOIN 语句

mysql - 使用 GROUP BY 的 SQL JOIN 语句

问题描述

问题：

构造 SQL 语句以查找每次会议的参加者人数。显示以下列：

该数据库中有 5 个表（person、building、room、meeting、person_meeting

+-----------+------------+------------+
| person_id | first_name | last_name  |
+-----------+------------+------------+
|         1 | Tom        | Hanks      |
|         2 | Anne       | Hathaway   |
|         3 | Tom        | Cruise     |
|         4 | Meryl      | Streep     |
|         5 | Chris      | Pratt      |
|         6 | Halle      | Berry      |
|         7 | Robert     | De Niro    |
|         8 | Julia      | Roberts    |
|         9 | Denzel     | Washington |
|        10 | Melissa    | McCarthy   |
+-----------+------------+------------+

+-------------+----------------------+
| building_id | building_name        |
+-------------+----------------------+
|           1 | Headquarters         |
|           2 | Main Street Buidling |
+-------------+----------------------+

+---------+-------------+-------------+----------+
| room_id | room_number | building_id | capacity |
+---------+-------------+-------------+----------+
|       1 | 100         |           1 |        5 |
|       2 | 200         |           1 |        4 |
|       3 | 300         |           1 |       10 |
|       4 | 10          |           2 |        4 |
|       5 | 20          |           2 |        4 |
+---------+-------------+-------------+----------+

+------------+---------+---------------------+---------------------+
| meeting_id | room_id | meeting_start       | meeting_end         |
+------------+---------+---------------------+---------------------+
|          1 |       1 | 2016-12-25 09:00:00 | 2016-12-25 10:00:00 |
|          2 |       1 | 2016-12-25 10:00:00 | 2016-12-25 12:00:00 |
|          3 |       1 | 2016-12-25 11:00:00 | 2016-12-25 12:00:00 |
|          4 |       2 | 2016-12-25 09:00:00 | 2016-12-25 10:00:00 |
|          5 |       4 | 2016-12-25 09:00:00 | 2016-12-25 10:00:00 |
|          6 |       5 | 2016-12-25 14:00:00 | 2016-12-25 16:00:00 |
+------------+---------+---------------------+---------------------+

+-----------+------------+
| person_id | meeting_id |
+-----------+------------+
|         1 |          1 |
|        10 |          1 |
|         1 |          2 |
|         2 |          2 |
|         3 |          2 |
|         4 |          2 |
|         5 |          2 |
|         6 |          2 |
|         7 |          2 |
|         8 |          2 |
|         9 |          3 |
|        10 |          3 |
|         1 |          4 |
|         2 |          4 |
|         8 |          5 |
|         9 |          5 |
|         1 |          6 |
|         2 |          6 |
|         3 |          6 |
+-----------+------------+

我的 SQL 语句：

SELECT Count(person_id) AS “Count of meeting attendees” ,meeting_id,meeting_start,meeting_end    
FROM meeting M ,person_meeting PM    
WHERE M. meeting_id=PM. meeting_id    
Group by PM.meeting_id,M.meeting_start,M.meeting_end;

我得到的错误：

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'of meeting attendees” ,meeting_id,meeting_start,meet
ing_end



FROM meeting M ' at line 1

请帮助我，我不确定我做错了什么。我无法找到解决此问题的方法

标签： mysqlsql

解决方案

切勿在FROM子句中使用逗号。 始终使用正确、明确、标准的JOIN语法。

不要给你需要转义的列别名。所以，使用下划线而不是空格。这解决了奇怪的双引号字符的问题：

SELECT COUNT(pm.person_id) AS num_attendees,
       m.meeting_id, m.meeting_start, m.meeting_end    
FROM meeting m JOIN
     person_meeting PM    
     ON m.meeting_id = pm.meeting_id    
GROUP BY m.meeting_id, m.meeting_start, m.meeting_end ;

推荐阅读