Oracle SQL问题 [英] Oracle SQL Question

问题描述

所以我整夜都在做这件事-不太了解我的作业，可悲的是我的教授在周末没有空.过程如下:查找每个城市中显示的最新电影的标题.显示城市名称和最新电影名称(按城市名称和电影名称排序).

So I've been doing this all night - can't quite understand my homework, and sadly my professor is unavailable on the weekend. Here it goes: Find the titles of the newest movies shown in each city. Display the city name and the newest movie title ordered by city name and movie title.

这是我的餐桌上的声明(感谢大家今晚帮助我，我欠您很多时间).

Here are my table declares (and thank you to EVERYONE helping me out tonight, I owe you big time).

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)
 );

这是我到目前为止所拥有的(基于先前问题中其他StackOverflow成员的帮助):

Here is what I have so far (based on help from other StackOverflow members from earlier questions):

   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

很明显，我的问题是宣布最新的电影-我该如何处理?我是通过示例学习的-一旦有人向我展示了一种方法，我便可以将其应用于其他所有方法-请帮忙.

Obviously my issue is in declaring the newest movies - how can I account for this? I learn by example - Once someone shows me one way, I can apply it to everything else - Please help.

推荐答案

问题确实很棘手，因为要知道什么是最新"，唯一的方法是通过MOVIE.releasedate值，这对于所有城市都是相同的因为发布日期应该已经存储在SHOWNAT表中，用于每个城市&的组合电影.

The question is really poor, because the only way to know what is "newest" is by the MOVIE.releasedate value, which will be the same for all cities because the release date should've been stored in the SHOWNAT table for each combination of city & movie.

这将列出数据库中的所有电影，顶部是最新电影:

This will list all the movies in the database, the newest movie at the top:

  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 +，但可能超出您的课程范围):

To get only the newest movies, I'd use analytic functions (supported 8i+, but likely beyond the scope of your class):

  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 x.rank = 1更改为:

To get more than the topmost movie, change WHERE x.rank = 1 to:

WHERE x.rank <= 3

...以获取最近发行的前三部电影.进行更改以适合您的需求.

...to get the top three most recently released movies. Change to suit your needs.

这篇关于Oracle SQL问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！