當您不能使用 AND 關鍵字時，如何為查找表編寫“和”查詢？

我正在建立一個實踐網站，允許使用者根據酒店提供的便利設施搜尋酒店。從 FE 中，使用者將選擇他們想要的許多便利設施的複選框，並且便利設施key將被發送到後端。在後端，我有這三個表：

         hotels
| id | name         | vacancies
| ---| ------------ | -------- |
|   1| Marriott     |        0 |
|   2| Best Western |       10 |
|   3| Sheraton     |        3 |
------------------------------

         amenities
| id | name                  | key                   |
| ---| --------------------- | --------------------- |
|   1| Cafe                  | cafe                  |
|   2| Wheelchair Accessible | wheelchair_accessible |
|   3| Wifi                  | wifi                  |
----------------------------------------------------


   hotels_amenities_lookup
| id | amenity_id | hotel_id |
| ---| ---------- | -------- |
|   1|          1 |        3 |
|   2|          2 |        1 |
|   3|          2 |        2 |
|   4|          2 |        3 |
|   5|          3 |        2 |
|   6|          3 |        1 |
----------------------------

要搜尋一種便利設施，例如wheelchair_accessible，我會這樣做：

WITH hotels_with_amenity as (
   SELECT ha.hotel_id
     FROM hotels_amenities_lookup ha
     JOIN (
           SELECT id from amenities a 
           WHERE  a.key = 'wheelchair_accessible'
       ) amenity ON ha.amenity_id = amenity.id
)

SELECT  h.name, 
       h.vacancies 
  FROM hotels h, hotels_with_amenity hwa
 WHERE h.id = hwa.hotel_id;

返回所有三個酒店。

問題是：如果使用者選擇了多個設施，wheelchair_accessible例如wifi，我將如何查詢同時擁有這兩個設施的酒店？用這個目前的設置，我做不到 WHERE a.key = 'wheelchair_accessible AND a.key = 'wifi'

有沒有更好的方法來設置這些表以使查詢更容易？

我是關係數據庫的新手，很可能我在這裡遺漏了一些明顯的東西。

**關係劃分**的經典案例！

最有效的查詢技術之一：

SELECT h.name, 
      h.vacancies 
FROM   hotels_amenities_lookup ha1
JOIN   hotels_amenities_lookup ha2 USING (hotel_id)
-- more?
JOIN   hotels h ON h.id = ha1.hotel_id
WHERE  ha1.amenity_id = (SELECT id FROM amenities WHERE key = 'wheelchair_accessible')
AND    ha2.amenity_id = (SELECT id FROM amenities WHERE key = 'wifi')
-- more?

db<>fiddle here（重用 McNets fiddle，謝謝！）

這些指數有助於：

• amenity(key, id) – ①
• hotel_amenity(amenity_id, hotel_id)
• hotel_amenity(hotel_id, amenity_id)– ②
• hotel(id)– PK

① 可能的形式UNIQUE (key) INCLUDE (id)。有關的：

• PostgreSQL 中的覆蓋索引是否有助於 JOIN 列？

② 是的，另一個具有反轉列的多列索引。看：

• 複合索引是否也適用於第一個欄位的查詢？

但是還有很多其他的方法：

• 如何過濾 SQL 結果中的 has-many-through 關係

旁白1：上面是 using AND，但實際上並不是要避免使用關鍵字，是嗎？

旁白2：只是hotels_amenities代替hotels_amenities_lookup？

數據庫設計

但是，正如 Laurenz 所評論的，第一個問題是關於正確的數據庫設計。這在很大程度上取決於確切的（未公開的）規格和要求。

您完全規範化的多對多實現是一個“包羅萬象”的解決方案。您可以根據需要組合任意數量的酒店和設施，輕鬆重命名、添加、刪除酒店和設施，在每個表中添加任意數量的附加資訊，並且使用普通 btree 索引進行索引很簡單。對任何或所有表中的許多更新具有強大的並發寫入負載。但是磁碟佔用空間很大。使用許多便利設施優化您的查詢需要一些工作。

為了組合一個充滿便利的手，呈現的查詢是快速的，並且由查詢計劃器自動優化（使用列統計中的 MCV 列表）。對於更多的便利設施（更多join_collapse_limit），您必須切換到更智能的解決方案，如您找到的相關答案中概述的：

• 在 WHERE 子句中多次使用同一列

解決方案的關鍵是首先過濾稀有的便利設施。

關於join_collapse_limit：

• Postgres JOIN 條件與 WHERE 條件
• 隱式連接是否與 Postgres 中的顯式連接一樣有效？

OTOH，對於一組小而穩定的便利設施，像 Laurenz 建議的帶有布爾標誌的簡單設計具有更小的磁碟佔用空間。也許為每個標誌添加一個部分索引。

或者一系列"char"[]帶有 GIN 索引的選項（使用 ? 優化）的便利設施。甚至可以以MATERIALIZED VIEW.

每個都有匹配的查詢樣式。

如果儲存空間很重要（它總是以某種方式）：

• 我應該使用 PostgreSQL 位串嗎？

一如既往：這取決於。

一種解決方案可能是使用 GROUP BY + HAVING COUNT。

select
 hal.hotel_id,
 count(*) as num_amenities
from
 hotels_amenities_lookup hal
join
 amenities am
 on am.id = hal.amenity_id
where
 am.key = 'wifi'
 or 
 am.key = 'wheelchair_accessible'
group by
 hal.hotel_id
having
 count(*) = 2;

hotel_id | num_amenities
-------: | ------------:
      1 |             2
      2 |             2

此模型的缺點是條件在您的查詢中是硬編碼的，對於通用表單，您可以使用帶有所選設施的新（臨時）表。

創建表 selected_amenities(id int);

插入 selected_amenities 值 (2),(3);

現在您可以通過這種方式將此表添加到上一個查詢中：

select
 h.id,
 h.name,
 h.vacancies,
 count(*) as num_amenities
from
 hotels_amenities_lookup hal
join
 hotels h
 on h.id = hal.hotel_id
join
 amenities am
 on am.id = hal.amenity_id
join
 selected_amenities sa
 on sa.id = am.id
group by
 h.id,
 h.name,
 h.vacancies
having
 count(*) = (select count(*) from selected_amenities);

編號 | 姓名 | 職位空缺 | num_amenities
-: | :----------- | --------: | ------------:
 2 | 最佳西方 | 10 | 2
 1 | 萬豪 | 0 | 2

db<>在這裡擺弄

引用自：https://dba.stackexchange.com/questions/287716