SQL 仅选择存在多个关系的行

2021-12-19 00:00:00 sql select mysql

给定一个父表'父'

╔═══════════╦══════════╗
║ PARENT_ID ║   NAME   ║
╠═══════════╬══════════╣
║         1 ║ bob      ║
║         2 ║ carol    ║
║         3 ║ stew     ║
╚═══════════╩══════════╝

以及父级和(此处未指定)属性表之间的多对多关系表rel"

and a many-many relationship table 'rel' between parent and a (here unspecified) property table

╔═══════════╦═══════════╗
║ PARENT_ID ║  PROP_ID  ║
╠═══════════╬═══════════╣
║         1 ║         5 ║
║         1 ║         1 ║
║         2 ║         5 ║
║         2 ║         4 ║
║         2 ║         1 ║
║         3 ║         1 ║
║         3 ║         3 ║
╚═══════════╩═══════════╝

如何选择具有所有一组指定关系的所有父级?例如.使用示例数据，如何找到同时拥有属性 5 和属性 1 的所有父母?

How can I select all parents that have all of a specified set of relationships? E.g. with the sample data, how can I find all parents that have both property 5 and 1?

同样的问题，但要求 完全 匹配:SQL 仅选择存在精确多重关系的行

edit: Same question but with requirement for an exact match: SQL Select only rows where exact multiple relationships exist

推荐答案

这叫做 关系划分

This is called Relational Division

SELECT  a.name
FROM    parent a
        INNER JOIN rel b
            ON a.parent_ID = b.parent_ID
WHERE   b.prop_id IN (1,5)
GROUP BY a.name
HAVING COUNT(*) = 2

  • SQLFiddle 演示链接

    • 更新 1

    如果没有对每个 parent_id 的 prop_id 强制实施 唯一约束，则在这种情况下需要 DISTINCT.

    if unique constraint was not enforce on prop_id for every parent_id, DISTINCT is needed on this case.

    SELECT  a.name
FROM    parent a
        INNER JOIN rel b
            ON a.parent_ID = b.parent_ID
WHERE   b.prop_id IN (1,5)
GROUP BY a.name
HAVING COUNT(DISTINCT b.prop_id) = 2

相关文章