Question

因此,我一夜总会这样做——能够非常了解我的家事,令人悲哀的是,本周末我无法教授。这里是: 选择每个城市放映的最新电影的名称。展示城市名称和按城市名称和电影名称订购的最新电影名称。

我在此宣布(并感谢你帮助我过夜,我给你多的时间)。

CREATE TABLE Theatres (
Name varchar2(50) not null,
City varchar2(50) not null,
State varchar2(50) not null,
Zip number not null,
Phone varchar2(50) not null,
PRIMARY KEY (Name)
);

CREATE TABLE Movies (
 Title varchar2(100) not null,
 Rating NUMBER not null,
 Length NUMBER not null,
 ReleaseDate date not null,
 PRIMARY KEY (Title),
 CHECK (Rating BETWEEN 0 AND 10),
 CHECK (Length > 0),
 CHECK (ReleaseDate > to_date( 1/January/1900 ,  DD/MONTH/YYYY ))
 );
CREATE TABLE ShownAt (
 TheatreName varchar2(50) not null,
 MovieTitle varchar2(100) not null,
 PRIMARY KEY (TheatreName, MovieTitle),
 FOREIGN KEY (TheatreName) REFERENCES Theatres(Name),
 FOREIGN KEY (MovieTitle) REFERENCES Movies(Title)
 );

这里是我迄今为止(根据来自先前问题的其他斯特克韦罗成员的援助):

   SELECT m.title AS m_title, 
          t.city,
          m.title
     FROM THEATRES t
     JOIN SHOWNAT sa ON sa.theatrename = t.name
     JOIN MOVIES m ON m.title = sa.movietitle
 GROUP BY t.city, m.title
 ORDER BY m_title DESC

显然,我的问题是宣布最新电影——我如何对此负责? 我以身作则学习——如果有人以某种方式向我表明,我可以将其应用于其他一切——请帮助。

Answer 1

问题确实很糟,因为了解什么是“西”的唯一途径是数值,所有城市的数值相同,因为公布日期应当存放在SHOWNAT的表格中,用于城市和城市与城市的组合;电影。

该数据库将列出所有电影,这是最新的电影:

  SELECT m.title,
         t.city
    FROM THEATRES t
    JOIN SHOWNAT sa ON sa.theatrename = t.name
    JOIN MOVIES m ON m.title = sa.movietitle
ORDER BY m.releasedate DESC, m.title, t.city

为了只接收最新电影,我使用分析功能(支持8i+,但可能超出你的类别):

  SELECT x.city, 
         x.title             
    FROM (SELECT t.city,
                 m.title,
                 ROW_NUMBER() OVER(PARTITION BY t.city
                                       ORDER BY m.releasedate) AS rank
            FROM THEATRES t
            JOIN SHOWNAT sa ON sa.theatrename = t.name
            JOIN MOVIES m ON m.title = sa.movietitle) x
   WHERE x.rank = 1
ORDER BY x.city, x.title

只接收最优秀的电影,修改<代码> WHERE xrank = 1 to:

WHERE x.rank <= 3

......获得最近发行的三部电影。 2. 适应你们的需要。

Answer 2

页: 1 每一电影的年价值。

彩虹将要求按条款组别,如果你说,在确定最大值时会使用。

Answer 3

Select m.Title, t.city 
from Theatres t 
inner join ShownAt s ON t.Name = s.TheatreName  
inner join Movies m ON s.MovieTitle = m.Title
order by m.ReleaseDate Desc, t.Name, m.Title

Answer 4

外部选择获得每个城市的最新展品,选择获得所有相应的电影。

select m.title, t.city, m.releasedate
from theatres t, movies m, shownat s
where t.name = s.theatrename and m.title = s.movietitle 
and (t.city, m.releasedate) in (
  select t1.city, max(m1.releasedate)
  from theatres t1, movies m1, shownat s1
  where t1.name = s1.theatrename and m1.title = s1.movietitle 
  group by t1.city
);

Answer 5

概述两部电影由于只有一部电影被送回,因此没有工作。如果(n +1)电影使用WHERE x.rank <=n,则使用相同日期。

友情链接