高分悬赏 SQL语句

来源：百度知道 编辑：UC知道 时间：2024/05/17 07:51:08
有3张表.Movie,origin和Country.Origin是Movie和Country之间的关联实体。
Movie的PK是MovieID，Country的PK是CountryID，MovieID和CountryID
作Origin的联合主键（同时也是外键）。
要求写以下表达：
For each year, show all countries that made more than 20 movies in that year together with the number of movies they made
求每一年制作超过20部电影的国家，并把具体数目也表示出来。

急！！！
请各位帮忙，回答得好会追加分数！

不知道用到是那种库,oracle下我以前写过类似的sql
select countryid,cc from (
select count(*) as cc,o.countryid from Movie m,Country c,Origin o
where m.movieid = o.movieid and c.countryid = o.countryid group by
o.countryid)
where cc>20

结果是:
countryid cc
001 30
002 40
003 50
....
..

sqlserver中可以用如下语句：

select 电影制作日期字段,CountryID,count(MovieID) as movieCount
from origin
group by CountryID,datepart(yy,电影制作日期字段)
having count(MovieID)>20

select y.Country as '国家',year(x.日期) as '年份',y.movieCount '影片数量' from (select 日期,CountryID,count(1) as movieCount
from origin
group by CountryID,year(日期)
having count(1)>20) x,Country y
where x.CountryID=y.CountryID

count(1)可以提高效率

select count(*) as cc,o.countryid from Movie m,Country c,Origin o
where m.movieid = o.movieid and c.countryid = o.countryid group by
o.countryid)
where