mysql - 了解 GROUP BY 语句的行为

Question

问题是..

表格是这个..

+--------------------------+---------+------+-----+---------+----------------+
| Field                    | Type    | Null | Key | Default | Extra          |
+--------------------------+---------+------+-----+---------+----------------+
| facility_map_id          | int(10) | NO   | PRI | NULL    | auto_increment |
| facility_map_facility_id | int(10) | NO   | MUL | NULL    |                |
| facility_map_listing_id  | int(10) | NO   |     | NULL    |                |
+--------------------------+---------+------+-----+---------+----------------+

数据是这样的..

+-----------------+--------------------------+-------------------------+
| facility_map_id | facility_map_facility_id | facility_map_listing_id |
+-----------------+--------------------------+-------------------------+
|             248 |                        1 |                      18 |
|             259 |                        1 |                      19 |
|             206 |                        1 |                      20 |
|             244 |                        1 |                      21 |
|             249 |                        2 |                      18 |
|             207 |                        2 |                      20 |
|             208 |                        3 |                      20 |
|             245 |                        3 |                      21 |
|             260 |                        4 |                      19 |
|             261 |                        5 |                      19 |
|             246 |                        6 |                      21 |
|             250 |                        7 |                      18 |
|             247 |                        8 |                      21 |
+-----------------+--------------------------+-------------------------+

我运行这个查询:

SELECT facility_map_listing_id 
FROM facility_map 
WHERE facility_map_facility_id IN(1, 2) 
GROUP BY facility_map_listing_id 
HAVING count(DISTINCT facility_map_facility_id) >= 2 

得到这个..

+-------------------------+
| facility_map_listing_id |
+-------------------------+
|                      18 |
|                      20 |
+-------------------------+
2 rows in set (0.00 sec)

这是正确的! - 但谁能解释一下，为什么 GROUP BY 需要出现在声明中？

如果它不是并且我运行相同的查询而忽略了 GROUP BY 我得到..

+-------------------------+
| facility_map_listing_id |
+-------------------------+
|                      18 |
+-------------------------+
1 row in set (0.00 sec)

谁能给我解释一下吗？谢谢!

Answer 1

如果没有group by，count 之类的聚合将作为一个整体对集合进行处理。所以这个查询返回零行或一行:

SELECT facility_map_listing_id 
FROM facility_map 
WHERE facility_map_facility_id IN(1, 2)
HAVING count(DISTINCT facility_map_facility_id) >= 2 

如果满足 having 条件，它将返回一行，否则返回一个空集。

现在，通过分组依据，它为 facility_map_listing_id 的每个值评估 having 条件。这可以返回与 facility_map_listing_id 的不同值一样多的行。