对于一年中有不止一部电影上映的演员来说，把他们拍摄过的电影打印出来

Question

我有一个电影表(id(pk), movie_name, year_of_release, language), actors table(id(pk), actor_name, gender, dob), actors_in_movies table (id(pk), movie_id, actor_id)

问题是在POSTGRES中使用sql子查询对于一年中有不止一部电影上映的演员，并将他们拍摄的电影打印为输出。

演员：

**id  actor_name          dob     gender**
1   "Shah Rukh Khan"    1965    "M"
2   "Deepika Padukon"   1984    "F"
3   "Johnny Depp"   1975    "M"

电影：

**id   movie_name  year_of_release  language**
1   "Sholay"    1993    "Hindi"
2   "Taare Zameen Par"  2008    "Hindi"
3   "Avengers End Game" 2010    "English"
4   "Squid Game"    2008    "Korean"
5   "Boys Over Flower"  2010    "Korean"
6   "The Family Man"    2010    "Hindi"
7   "Ask Laftan Anlamaz"    2016    "Turkish"
8   "Dangal"    2010    "Hindi"

actors_in_movies

**id movie_id actor_id**
1        1       1
2        3       1
3        2       2
4        3       3
5        4       1
6        6      2
7       7      1
8       5       2
9       8       3

预期输出：

**movie_name   year_of_release   actor_name**
avengers end game   2010       johny depp
dangal              2010        johny depp
the family man        2010      deepika padukon
boys over flower      2010      deepika padukon

Answer 1

with cte as(
select c.id,year(year_of_release),count(b.*)
from actors c 
left join actors_in_movies b on c.id=b.actor_id 
left join movies a on a.id=b.movie_id
group by c.id,year_of_release
having count(b.*)>1)
select c.*
from cte a inner join actors_in_movies b on a.id=b.actor_id 
inner join movies c on b.movie_id=c.id

Answer 2

Year函数基于您正在使用的机器。这里使用MSSQL函数Year。如果您使用的是Postgres，只需将year(year_of_release)替换为to_char(year_of_release,'YYYY-MM-DD')即可。