作者:手机用户2502875927 | 来源:互联网 | 2023-06-03 09:00
我想你可以用
存在
而不是JOIN。
SELECT m.year, COUNT(m.id) FROM movies m
where
exists (select * from roles r where r.movie_id=m.id and
exists(select * from actors a where a.id=r.actor_id and a.gender='F'))
group by m.year;
显示每年的电影总数以及以上输出。
select t1.year,t1.count,t2.total from
(
SELECT m.year as year, COUNT(m.id) as count FROM movies m
where exists (select * from roles r where r.movie_id=m.id and exists(select * from actors a where a.id=r.actor_id and a.gender='F'))
group by m.year
)t1
join
(select year,count(m.id) as total from movies m group by m.year) t2
on t1.year=t2.year;