Scehma https://i.stack.imgur.com/5mr4u.jpg
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;
我没有得到正确的输出。我得到零行。有人可以修改上面的查询并给我正确的输出,我尝试了各种查询但没有得到任何结果
检查一下:
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
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)