如何根據多個標籤過濾多個多對多關係？

我有兩個主表的方案：problem和tag一個關係（這是一個多對多連接器）表：problem_tags它們的摘錄如下：

問題表：

----+------------------------------------+--------
id |                name                | rating 
----+------------------------------------+--------
 1 | Special Permutation                |   1600
 2 | Binary String Reconstruction       |   1500
 3 | Special Elements                   |   1500
 4 | Alice, Bob and Candies             |   1300
 5 | K-th Not Divisible by n            |   1200
 6 | Same Parity Summands               |   1200
 7 | Sum of Round Numbers               |    800
 8 | Skier                              |   1400
 9 | Square?                            |    900

標記表：

id |           name            
----+---------------------------
 1 | constructive algorithms
 2 | dfs and similar
 3 | math
 4 | brute force
 5 | implementation
 6 | two pointers
 7 | binary search
 8 | data structures

問題標籤表：

problem_id | tag_id 
------------+--------
         1 |      1
         2 |      1
         2 |      2
         2 |      3
         3 |      4
         3 |      5
         3 |      6
         4 |      5
         5 |      3
         5 |      7

我的問題是如何根據多個標籤過濾掉問題，即所有標記為數學和二進制搜尋和蠻力的問題；或所有標記為數學但不是構造算法的問題；或者對於更複雜的問題，所有問題都只用數學和實現來標記，沒有別的？

目前我想出了這樣的事情：

1. 查找所有標記為math的問題 id （加入 tag 和 question_tags 表）
2. 查找標記為二分搜尋的所有問題的 id
3. 查找所有標記為蠻力的問題 id
4. 獲取以上所有id的交集
5. 選擇其 id 位於上述交叉點的問題

但是我的解決方案在到達第二個範例時缺乏（僅用選定的標籤標記），我認為這不是最優化和SQL-ish 的方式。

我剛剛在另一個論壇上問過這個問題。第三範式模式設計的最佳解決方案是選擇連結表中所需的所有內容並比較匹配結果的基數（計數）。

另一個論壇是甲骨文。您尋求的答案是這篇文章的 PostgreSQL 版本：https ://community.oracle.com/message/15613817#15613817

-- copy paste from original post
-- Oracle version
select *     
from my_recipes rp    
where rp.recipe_id  in  
(    
   select tg.recipe_id     
   from my_recipe_tags tg    
   where tg.tag_id in    (
                           -- expands string '1:2:3' into a table w/ 3 row    
                           select to_number(regexp_substr(:p_search_tags,'[^:]+', 1, level) )    
                           from  dual    
                           connect by regexp_substr(:p_search_tags, '[^:]+', 1, level) is not null    
                       )   
   group by tg.recipe_id                         
                     -- counts cardinality of Tags in search
   having count(*) = regexp_count(:p_search_tags, '[^:]+')                               
) ;

未經測試（需要修復）

select *
from PROBLEMS p
where p.id in (
   select pt.problem_id
   from PROBLEM_TAGS pt
       join TAGS t on pt.tag_id = t.tag_id
   where t.name in ('math','binary search', 'brute force') -- should be from a CTE
   group by pt.problem_id
   having count(*) = 3 -- count(*) from CTE
)

對於第二個版本，添加AND p.id NOT IN子句。

對於第 3 個版本，添加一個過濾器，以確保pt.problem_id具有查詢標籤的確切基數。

and p.id in (
   select pt.problem_id
   from PROBLEM_TAGS pt
   group by pt.problem_id
   having count(*) = 2 -- you have two tags for 3rd one
                       -- you should pull Cardinality from CTE also
)

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