查找使用Yash Chopra制作的电影多于其他任何导演的所有演员

Question

塞赫玛

    SELECT   p1.pid, 
         p1.NAME, 
         Count(movie.mid) AS movieswithyc 
FROM     person           AS p1 natural 
JOIN     m_cast natural 
JOIN     movie 
JOIN     m_director 
ON       ( 
                  movie.mid = m_director.mid) 
JOIN     person AS p2 
ON       ( 
                  m_director.pid = p2.pid) 
WHERE    p2.NAME LIKE 'Yash Chopra' 
GROUP BY p1.pid 
HAVING   Count(movie.mid) >ALL 
         ( 
                    SELECT     Count(movie.mid) 
                    FROM       person AS p3 natural 
                    JOIN       m_cast 
                    INNER JOIN movie 
                    JOIN       m_director 
                    ON         ( 
                                          movie.mid = m_director.mid) 
                    JOIN       person AS p4 
                    ON         ( 
                                          m_director.pid = p4.pid) 
                    where      p1.pid = p3.pid 
                    AND        p4.NAME NOT LIKE 'Yash Chopra' 
                    GROUP BY   p4.pid) 
ORDER BY movieswithyc DESC;

我没有得到正确的输出。 我的行数为零。有人可以修改上面的查询并给我正确的输出，但我尝试了各种查询，但没有得到任何结果

Answer 1

您可以检查以下SQL。

说明-第一个内联视图返回带有“ Yash Chopra”的电影计数的人员列表。第二个内联视图返回与其他导演一起观看电影的人的列表。最后，我筛选了“ Yash
Chopra”的电影数量大于“其他导演”的那些人的列表。

(select pc.name, count(distinct m.mid) count_movie
from movie m
join m_cast mc on m.mid = mc.mid
join m_director md on m.mid = md.mid
join person pc on mc.pid = pc.pid
join person pd on md.pid = pd.pid
where pd.name = 'YASH CHOPRA'
group by pc.name) lst_yc
join
(select pc.name, count(m.mid) count_movie
from movie m 
join m_cast mc on m.mid = mc.mid
join m_director md on m.mid = md.mid
join person pc on mc.pid = pc.pid
join person pd on md.pid = pd.pid
where pd.name != 'YASH CHOPRA'
group by pc.name) lst_wo
on lst_yc.name = lst_wo.name
where lst_yc.count_movie > lst_wo.count_movie