检查此项：

SELECT first.actor, 
       first.count 
FROM   (SELECT Trim(actor) AS Actor, 
               Count(*)    AS COUNT 
        FROM   m_cast mc 
               INNER JOIN (SELECT m.mid 
                           FROM   movie m) AS m 
                       ON m.mid = Trim(mc.mid) 
               INNER JOIN (SELECT md.pid, 
                                  md.mid 
                           FROM   m_director md) AS md 
                       ON md.mid = Trim(mc.mid) 
               INNER JOIN (SELECT p.pid, 
                                  p.NAME AS actor 
                           FROM   person p) AS pactor 
                       ON pactor.pid = Trim(mc.pid) 
               INNER JOIN (SELECT p.pid, 
                                  p.NAME AS director 
                           FROM   person p) AS pdirector 
                       ON pdirector.pid = Trim(md.pid) 
        WHERE  director LIKE '%Yash Chopra%' 
        GROUP  BY Trim(actor)) first 
       LEFT JOIN (SELECT actor, 
                         Max(count) AS COUNT 
                  FROM   (SELECT DISTINCT Trim(actor) AS Actor, 
                                          Count(*)    AS COUNT 
                          FROM   m_cast mc 
                                 INNER JOIN (SELECT m.mid 
                                             FROM   movie m) AS m 
                                         ON m.mid = Trim(mc.mid) 
                                 INNER JOIN (SELECT md.pid, 
                                                    md.mid 
                                             FROM   m_director md) AS md 
                                         ON md.mid = Trim(mc.mid) 
                                 INNER JOIN (SELECT p.pid, 
                                                    p.NAME AS actor 
                                             FROM   person p) AS pactor 
                                         ON pactor.pid = Trim(mc.pid) 
                                 INNER JOIN (SELECT p.pid, 
                                                    p.NAME AS director 
                                             FROM   person p) AS pdirector 
                                         ON pdirector.pid = Trim(md.pid) 
                          WHERE  director NOT LIKE '%Yash Chopra%' 
                          GROUP  BY Trim(actor), 
                                    director) 
                  GROUP  BY actor) second 
              ON first.actor = second.actor 
WHERE  first.count >= second.count 
        OR second.actor IS NULL 
ORDER  BY first.count DESC

您可以检查下面的sql。
说明-第一个内联视图返回带有“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

SELECT * 
FROM   ( 
                SELECT   pc.NAME, 
                         Count(DISTINCT Trim(m.mid)) count_movie 
                FROM     movie m 
                JOIN     m_cast mc 
                ON       Trim(m.mid) = Trim(mc.mid) 
                JOIN     m_director md 
                ON       Trim(m.mid) = Trim(md.mid) 
                JOIN     person pc 
                ON       Trim(mc.pid) = Trim(pc.pid) 
                JOIN     person pd 
                ON       trim(md.pid )= Trim(pd.pid) where pd.NAME = 'Yash Chopra' GROUP BY pc.NAME) lst_yc
                JOIN 
                         ( 
                                  SELECT   pc.NAME, 
                                           count(trim(m.mid)) count_movie 
                                  FROM     movie m 
                                  JOIN     m_cast mc 
                                  ON       trim(m.mid) = trim(mc.mid ) 
                                  JOIN     m_director md 
                                  ON       trim(m.mid) = (md.mid) 
                                  JOIN     person pc 
                                  ON       trim(mc.pid) = trim(pc.pid) 
                                  JOIN     person pd 
                                  ON       trim(md.pid) = trim(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

这似乎是山塔努先生给出的答案。但是你知道为什么这需要时间吗，我在一个小时前运行了这个查询，但还没有reult生成。

p2.name像'yash chopra'和p1.pid这是代码中的一行。您应该像这样编写trim（p2.name）、trim（p1.pid），因为movie表中的name和pid包含空格之类的内容。您应该正确处理它，否则它将返回零行，请记住这一点。

select p.name,h.count 
from(select mc.pid as mcpid,md.pid as mdpid,count(mc.MID) as count   
from m_cast as mc
                 join m_director md 
                     on md.MID=mc.MID               
                 group by mc.pid ,md.pid 
                ) h
            join person p 
                on h.mcpid=p.pid
            where h.count = (select count(*) as count   
                             from m_cast as mc
                             join m_director md 
                                 on md.mid=mc.mid
                             where mc.pid=h.mcpid 
                             group by mc.pid,md.pid 
                             order by count(*) desc
                             limit 1)
            and h.mdpid = (select pid 
                           from person 
                           where name like '%Yash Chopra%'
                          )
            order by h.count desc