sql - SQL 查找至少放映过 2 次的电影

Question

好吧，在我学习的时候，我遇到了一个颠簸。

我有一个数据库包含

City(Name,State,Population)

Theatre(Theatre_id,name,City_Name)

ScreeningRoom(ScreeningRoom_Number,theatre_tid,Capacity)

Movie(Movie_id,title,lenght)

Screening(Screening_id,Movie_fid,SreeningR_number,viewers)

我需要找到每个放映室中至少放映过 2 次的所有电影。我设法为每个放映室放映的所有电影找到了一个解决方案，我的代码是这样的

select * from movie
where not exists
    (
    select * from ScreeningRoom 
    where not exists(
        select * from Screening
        where movies.movie_id=screeing.Movie_fid
        and ScreeningRoom.ScreeningRoom_Number=Screening.Screening_id
    )
    )
group by movie.title

但我不知道如何实施至少 2 次部分...请帮助

标签： sql

Answer 1

尝试这个。我在语句中添加了一个有子句。

select * from movie
where not exists
    (
    select * from ScreeningRoom 
    where not exists(
        select * from Screening
        where movies.movie_id=screeing.Movie_fid
        and ScreeningRoom.ScreeningRoom_Number=Screening.Screening_id
        having count(Screening.Screening_id) >1
    )
    )
group by movie.title