SQL-多对多的选择?

保持数据库正常化总是更好的。在您的特定情况下,我将存储每个复合元素的元素数,而不是为每个元素添加新行。

 compound_id element_id      bond         count
 -------------------------------------------------
   "Water"     "H"        "Covalent"        2
   "Water"     "O"        "Covalent"        1

精确匹配的查询将是

 select compound_id
 from elements
 group by compound_id
 having count(
              case when 
                (element_id = 'H' and count = 2) or
                (element_id = 'O' and count = 1) then 1 
              end
        ) = count(*)

然而,这种方法将是次优的,因为将使用顺序扫描。如果非规范化不是一个问题,那么为每种化合物存储许多不同的元素会有所帮助。

 compound_id   element_count
 ------------------------------
   "Water"          2

那么查询可能是

 select e.compound_id
 from elements e
 join compounds c on e.compound_id = c.compound_id
 where c.element_count = 2 and
       ((e.element_id = 'H' and e.count = 2) or
        (e.element_id = 'O' and e.count = 1))
 group by e.compound_id
 having count(*) = 2

如果你有一个索引 compounds(element_count) 和 elements(element_id, count) 然后查询将使用它快速检索结果,即使数据库很大。

为什么不只用 array_agg() ?

select compound_id
from t3
group by compound_id
having array_agg(element_id order by element_id) = array['H', 'H', 'O']