参与 A OR B 的 SQLite 人员

Question

老实说，这是一个家庭作业问题，但我已经浪费了好几个小时，而且无法正确解决。它返回错误数量的结果或错误的数据:

我需要选择参与指导电影和/或在电影中表演的每个人以及他们所做的次数，如果至少 5 次。

有一个acts带有 movie_id 的表和 person_id , directs与 movie_id和 person_id和 person ( pid 和 name )。

我想出的是:

 SELECT p.name,count(d.mid)+count(a.mid) 
    FROM person p
    INNER JOIN  acts a
    ON a.pid=p.pid

    LEFT JOIN  directs d
    ON p.pid=d.pid

    group by p.pid,p.name

    having (count(d.mid) +count(a.mid))>=5

基本上，我正在尝试加入 person和 directs与 person_id 匹配的表在两个表中，同时对 directs 执行相同操作.问题是第二个查询的结果不包括在内(有 10 部电影的 Alfred Hitchcock 从一开始就一直困扰着我)。

我正在使用 sqlite，如果有人可以插入我朝着正确的方向前进，我会非常高兴。我还被指示避免聚合子查询。我有一个使用它们的解决方案，但执行需要 15 分钟。

Answer 1

就这个问题而言， Actor 和导演之间没有相关的区别，都是当事人。

所以让我们create a view这隐藏了相关人员存储在两个表中的事实:

CREATE VIEW involved AS
SELECT mid, pid, 'actor'    AS role FROM acts
UNION ALL
SELECT mid, pid, 'director' AS role FROM directs

然后可以通过与 person 的简单连接来解决问题。 . ( role 列实际上并不需要。)

注意:这个问题没有说明如果某个人同时是同一部电影的 Actor 和导演会发生什么。如果您忽略 role字段和使用 UNION而不是 UNION ALL ，这将仅计为一次参与。但把它算作两个对我来说更有意义。

另一个注意事项:数据库结构针对您的问题设计得很糟糕；参与应该首先存储在一个表中。但在现实生活中，您并不总是能够更改数据库结构。 (您可能甚至不允许创建 View ，在这种情况下，您必须使用临时 View ，或者将 View 写为子查询。)