如何构造此查询...？

Question

考虑下表.

hotel     facilities
1       internet
1       swimming pool
1       wi-fi
1       parking
2       swimming pool
2       sauna
2       parking
3       toilets
3       bungee-jumping
3       internet
4       parking
4       swimming pool

我只需要选择那些有停车场、游泳池和互联网的酒店...？

我算出了以下几点……

SELECT hotel
FROM   table
WHERE  facilties IN(internet, swimming pool, parking)

该查询选择至少有一个选择的酒店。

但我需要的是一个查询，它选择拥有所有选定设施的酒店...

谢谢你的建议...

编辑:我还应该提到，选择的数量是由用户提供的，查询应该在运行时动态构建……

Answer 1

SELECT first.hotel
FROM table AS first
INNER JOIN table AS second
  ON first.hotel=second.hotel
INNER JOIN table AS third
  ON second.hotel=third.hotel
WHERE first.facilities='internet'
  AND second.facilities='swimming pool'
  AND third.facilities='parking'

Answer 2

有三种方法可以做到这一点：

连接

SELECT hotel
FROM table t1
JOIN table t2 ON t1.hotel = t2.hotel AND t2.facilities = 'swimming pool'
JOIN table t3 ON t1.hotel = t3.hotel AND t3.facilities = 'parking'
WHERE t1.facilities = 'internet'

聚合

SELECT hotel
FROM table
GROUP BY hotel
WHERE facilities IN ('internet', 'swimming pool', 'parking')
HAVING COUNT(1) = 3

注意：这假设没有重复的(酒店，设施)

存在

SELECT hotel
FROM table t
WHERE facilities = 'internet'
WHERE EXISTS (SELECT 1 FROM table WHERE hotel = t.hotel AND facilities = 'swimming pool')
AND EXISTS (SELECT 1 FROM table WHERE hotel = t.hotel AND facilities = 'parking')

一个好的SQL优化器可能会将它们都优化为相同的，但是我发现MySQL在这方面可能有些不可预测，所以您需要用有意义的数据集(>100万行)对它们进行基准测试，以确定哪个是最好的。

请参阅Oracle vs MySQL vs SQL Server: Aggregation vs Joins作为其中一些差异的示例。

Answer 3

你可以使用GROUP BY并拥有：

SELECT hotel
FROM table
WHERE facilties IN('internet', 'swimming pool', 'parking')
GROUP BY hotel
HAVING SUM(facilities = 'internet') AND SUM(facilities = 'swimming pool') AND SUM(facilities = 'parking')