找出所有和Yash Chopra一起拍电影比其他导演都多的演员
找出所有和Yash Chopra一起拍电影比其他导演都多的演员
提问于 2020-07-11 17:25:12
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;，我没有得到正确的输出。我得到零排。如果有人修改上面的查询并给我正确的输出，我尝试过各种查询，但没有得到任何。
回答 7
Stack Overflow用户
回答已采纳
发布于 2021-01-06 20:38:08
检查一下这个：
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 Stack Overflow用户
发布于 2020-07-11 17:37:10
您可以检查下面的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_movieStack Overflow用户
发布于 2020-07-11 18:39:45
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这似乎是Shantanu先生给出的答案。但是你知道为什么这需要时间吗?我在1小时前运行了查询，但还没有产生任何结果。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/62852386
复制相关文章
相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号