记录与SQL中其他表的所有记录相关的记录

假设我们在关系数据库中有三个表：

Person {id, name}
Obstacle {id, name}
Person_overcomes_obstacle {person_id, obstacle_id}

我想写一个查询，告诉我是否至少有一个人克服了所有障碍。想法？

已邀请:

4 个回复

佃蒜狗掂哥

我注意到我是唯一使用natural1ѭ的自然表别名的人！您需要一个关系除法查询。您可以在Person_overcomes_obstacle中计算障碍和匹配记录，并返回2个数字匹配的记录或以另一种方式查看它，因为找到没有障碍但没有克服的人。

SELECT p.id, p.name /*Or use COUNT(*) or wrap in Exists 
                     if you don't care about ids and names*/
FROM Person p
WHERE NOT EXISTS
   (SELECT * FROM Obstacle o
    WHERE NOT EXISTS 
    (
       SELECT * FROM Person_overcomes_obstacle poo
       WHERE poo.person_id = p.id and o.id = poo.obstacle_id
    )
)

茶鬼失形

SELECT 
    p.name,
    COUNT(DISTINCT oo.obstacle_id) AS OBSTACLES_COMPLETED
FROM 
    person p

    JOIN person_overcomes_obstacle oo
    ON oo.person_id = p.person_id

GROUP BY 
    p.name

HAVING 
    COUNT(DISTINCT poo.obstacle_id) = (SELECT COUNT(id) FROM obstacle)

僻朵庙惩竣

使用：

SELECT poo.person_id
    FROM PERSON_OVERCOMES_OBSTACLE poo
GROUP BY poo.person_id
    HAVING COUNT(DISTINCT poo.obstacle_id) = (SELECT COUNT(*) 
                                                                        FROM OBSTACLE)

这是一种不太可能表现良好的替代方案：

SELECT x.person_id
   FROM (SELECT poo.person_id,
                         COUNT(DISTINCT poo.obstacle_id) AS obs_overcome,
                         (SELECT COUNT(*) 
                             FROM OBSTACLE) AS obs_total
                 FROM PERSON_OVERCOMES_OBSTALCE poo
          GROUP BY poo.person_id) x
 WHERE x.obs_overcome = x.obs_total

在任何一种情况下，您都可以加入PERSON表以获取更多信息 - 或者您可以从任一查询中对person_id进行计数，以了解有多少人完成了所有记录的障碍。

漂汀拦

这个SELECT应该返回没有人克服的障碍数量。如果计数为零，那么至少一个人已经克服了所有障碍。

SELECT count(*)
  FROM (SELECT po.person_id, o.obstacle_id
          FROM Obstacle o
          LEFT OUTER JOIN Person_overcomes_obstacle po
            ON (o.obstacle_id = po.obstacle_id)) t
WHERE t.person_id IS NULL

您也可以这样做，以获得相同的效果（可能还有更好的性能）：

SELECT count(*)
  FROM Obstacle o
 WHERE NOT EXISTS (SELECT 1
                     FROM Person_overcomes_obstacle po
                    WHERE po.obstacle_id = o.obstacle_id)

编辑：正如评论中指出的那样，上述两个问题只能证明没有人能克服的障碍，而不是一个人克服了所有障碍。除此之外，仍然可以证明单个用户在不查询Person表的情况下克服了所有障碍：

SELECT t.personid, count(*)
  FROM (SELECT DISTINCT po.person_id, o.obstacle_id
          FROM Obstacle o
          JOIN Person_overcomes_obstacle po
            ON (o.obstacle_id = po.obstacle_id)) t
 GROUP BY t.persion_id
 HAVING count(*) = (SELECT count(*)
                      FROM obstacle)

要回复问题请先登录或注册

记录与SQL中其他表的所有记录相关的记录

4 个回复

发起人

relational_division

问题状态

记录与SQL中其他表的所有记录相关的记录

与内容相关的链接

4 个回复

发起人

relational_division

问题状态