mysql - 使用 SQL 返回匹配使用连接表的数组中所有项目的结果

Question

我在 SQL 查询上遇到困难，需要您的帮助。用简单的英语来说，我需要退回所有提供包含我选择的所有项目的套餐的酒店。

涉及的表有:

• 酒店(酒店编号、酒店名称)
• 套餐(package_id、hotel_id、package_name)
• 项目(item_id、package_id、item_name)
• package_items(package_id、item_id)

示例数据

为了帮助解释我的问题，让我们看一下这个示例数据以更清楚地说明它。

酒店表

----------------------hotel_id | hotel_name----------------------1        | Hilton2        | Westin----------------------

Packages table

-----------------------------------------package_id | hotel_id | package_name-----------------------------------------1          | 1        | Gold Package2          | 1        | Silver Package3          | 2        | Star Package4          | 2        | Ultimate Package-----------------------------------------

Items table

-------------------------------item_id | item_name-------------------------------1       | Free Room Upgrade2       | Dinner Included3       | Spa Access4       | Complimentary Papers-------------------------------

Package Items table

---------------------package_id | item_id---------------------1          | 21          | 31          | 42          | 22          | 33          | 44          | 14          | 24          | 34          | 4---------------------

So, using this data you can see that for example, the Hilton's Gold Package contains Dinner Included, Spa Access, and Complimentary Papers.

I'm building a feature on our frontend that lets the user filter for these various package items. A check box against each item, which when clicked should filter the results to show only the hotels which offer packages that contains ALL of the items selected by the user.

I'm a little stumped on the SQL for this. So far my research has brought me to the following sample code (found here: PostgreSQL where all in array):

SELECT conversation_id FROM conversations_users

 WHERE user_id IN (1,2)

 GROUP BY conversation_id HAVING COUNT(*) = 2

在此示例查询中，查询的关键部分是 HAVING COUNT() = 2 部分。有了这个，我希望强制查询以确保必须匹配所有选定的项目，而不仅仅是一个，这是没有 HAVING COUNT() = 2 部分的查询的默认行为。

使用这种方法和上面的表结构，我已经调整了这段代码以满足我们自己的要求，包括各种 JOIN 查询等。在下面的示例中，我选择了包含晚餐、水疗中心和免费论文。我希望回到希尔顿和威斯汀酒店，因为这两家酒店都提供包含所有这些项目的套餐。在希尔顿的情况下，他们的黄金套餐提供我选择的内容，而在威斯汀的情况下，他们的终极套餐提供我选择的内容。

请记住，我正在寻找提供包含我正在搜索的所有项目的套餐的酒店。

这是我到目前为止所得到的:

SELECT h.hotel_id, h.hotel_name FROM hotels h

  JOIN packages p       ON h.hotel_id   = p.hotel_id
  JOIN package_items pi ON p.package_id = pi.package_id
  JOIN items i          ON pi.item_id   = i.item_id

 WHERE i.item_id IN (2,3,4)

 GROUP BY h.hotel_id HAVING COUNT (*) = 3;

不幸的是，我在这里没有得到准确的结果，我对如何修复它感到困惑。非常感谢任何能为我解决这个问题的人。

Answer 1

将您的最后一个查询更新为:

     SELECT h.hotel_id, h.hotel_name FROM hotels h
     JOIN packages p       ON h.hotel_id   = p.hotel_id
     JOIN package_items pi ON p.package_id = pi.package_id
     JOIN items i          ON pi.item_id   = i.item_id
     WHERE i.item_id IN (2,3,4)
     GROUP BY h.hotel_id 
     HAVING COUNT (DISTINCT i.item_id) = 3;

这将为您返返回价中包含 3 个不同项目的酒店。