sql查询返回与每个参与者合作最多的参与者的姓氏

ubof19bj 于 2021-08-13 发布在 Java

关注(0)|答案(1)|浏览(451)

我是sql新手，我正在尝试编写一个查询来返回与每个参与者合作最多的参与者的姓氏。
我有三张table

Actor- id, first_name,last_name
Movie- id, title
Cast- pid, mid

这是我现在得到的，但它不起作用：

SELECT tt.ll
FROM Actor as AA ,Movie,Cast,(select a.id as idd, a.first_name,a.last_name as ll, count(*)
from Actor a
join Cast c on c.pid = a.id
where mid in (select mid from Cast where pid = AA.id) 
and pid <> AA.id 
group by a.id
order by count(*) desc, a.last_name ASC limit 1) tt
WHERE pid=AA.id AND Cast.mid=Movie.id 
group by AA.id;

sql mysql Database video

来源：https://stackoverflow.com/questions/62115890/sql-query-to-return-the-last-name-of-actor-who-has-worked-with-each-actor-the-mo

1条答案

按热度按时间

yyyllmsg1#

你可以得到一对演员一起工作的次数 cast 或相关子查询：

select c.aid, c2.aid, count(*)
from cast c join
     cast c2
     on c.mid = c2.mid   -- same movie
group by c.aid, c2.aid

要获得第一列中每个参与者的最大收益，请使用窗口函数：

select aa.*
from (select c.aid, c2.aid as aid_2, count(*) as cnt,
             row_number() over (partition by c.aid order by count(*) desc) as seqnum
      from cast c join
           cast c2
           on c.mid = c2.mid   -- same movie
      group by c.aid, c2.aid
     ) aa
where seqnum = 1;

然后填写电影，你可以用两个 join 我们去 actors table。我让你来做。

赞(0）回复(0）举报 2021-08-13

我来回答

sql查询返回与每个参与者合作最多的参与者的姓氏

1条答案

相关问题

热门标签

最新问答