Question

我对这一询问感到不安,认为这是斯坦福数据库MOOC的一项活动:

对于同一审查者两次评定同一部电影并第二次给予更高评级的所有情况,审查者的姓名和电影名称均予归还。

作为这项工作的一部分,正在使用三个表格:movie, Remuneration and reviewer。正在使用的系统是SQLite。

movie;
+----------+---------+------+-----+---------+-------+
| Field    | Type    | Null | Key | Default | Extra |
+----------+---------+------+-----+---------+-------+
| mID      | int(11) | YES  |     | NULL    |       |
| title    | text    | YES  |     | NULL    |       |
| year     | int(11) | YES  |     | NULL    |       |
| director | text    | YES  |     | NULL    |       |
+----------+---------+------+-----+---------+-------+

rating;
+------------+---------+------+-----+---------+-------+
| Field      | Type    | Null | Key | Default | Extra |
+------------+---------+------+-----+---------+-------+
| rID        | int(11) | YES  |     | NULL    |       |
| mID        | int(11) | YES  |     | NULL    |       |
| stars      | int(11) | YES  |     | NULL    |       |
| ratingDate | date    | YES  |     | NULL    |       |
+------------+---------+------+-----+---------+-------+

reviewer;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| rID   | int(11) | YES  |     | NULL    |       |
| name  | text    | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+

Expected Query Result:
name            title         
Sarah Martinez  Gone with the Wind

如果任何人想要获得数据,。

Answer 1

SELECT
   W.name,
   M.title
FROM
   reviewer AS R
   INNER JOIN movie AS M
      ON EXISTS ( -- there is at least one rating
         SELECT *
         FROM rating AS G
         WHERE
            -- by the reviewer and movie in question
            R.rID = G.rID
            AND M.mID = G.mID
            AND EXISTS ( -- for which another rating exists
               SELECT *
               FROM rating AS G2
               WHERE
                  -- for the same reviewer and movie
                  R.rID = G2.rID
                  AND M.mID = G2.mID
                  AND G.stars < G2.stars -- but rated higher
                  AND G.ratingDate < G2.ratingDate -- and later
            )
      )
;

如果Kallite允许ON,我不相信100%。备有<代码>EXISTS表述的条款。否则,你只能将<条码>EXISTS的表述移至<条码>。 WHERE 条款和在<条码>审查器和<条码>之间互联。

如果具体支持<代码>EXISTS,则将<代码>EXISTS 作为<代码>FROM条款的衍生表格的查询,有两张表格INNER JOIN,然后。按分类 mId and the rID, 然后INNER JOIN to the main table. 这可能是这样:

SELECT
   R.name,
   M.title
FROM
   (
      SELECT
         G.rID,
         G.mID
      FROM
         rating AS G
         INNER JOIN rating AS G2
            ON G.rID = G2.rID
            AND G.mID = G2.mID
            AND G.stars < G2.stars
            AND G.ratingDate < G2.ratingDate
      GROUP BY
         G.rID,
         G.mID
   ) C
   INNER JOIN reviewer AS R
      ON C.rID = R.rID
   INNER JOIN movie AS M
      ON C.mID = R.mID
;

我希望你能够看到这两个问题如何表达同样的语义。在一个非常庞大的数据库里,人们多次对同一电影进行评级,因此可能会出现业绩差异(<>EXISTS)。第一版显示,它能够发挥更好的作用,因为它一旦找到一个结果就能够停止。

说明:你可以加入整个光盘,成为单一查询和<代码>。按分类 http://www.un.org/Depts/DGACM/index_chinese.htm 分组应当尽早进行。

Answer 2

我已设法解决这一具体问题:

SELECT R.name, M.title
FROM
    Rating AS RatingLatest
JOIN Rating AS R2 
    ON RatingLatest.rID = R2.rID AND R1.mID = R2.mID
JOIN Reviewer AS R USING (rID)
JOIN Movie AS M USING (mID)
-- Check if there is a newer rating with more stars than the previous one 
WHERE RatingLatest.ratingDate > R2.ratingDate 
AND RatingLatest.stars > R2.stars

它收复审查员,如果它评定同一部电影不止一次,而且最后(不是第二部)评级更高。

Answer 3

同时:

select yes1.name,yes1.title from (select yes. name, yes. title,d.stars,d.rating date,(rank() over(partition by yes.name order by d.stars desc)) as rankk1,(rank() over(partition by yes.name order by d.ratingDate asc )) as rankk2
from (select c.mID,a.rID,a.name,c.title from Reviewer as a join Rating as b on a.rID=b.rID
join Movie as c on b.mID=c.mID group by a.name,c.title,c.mID,a.riD having count(*)=2) as yes join Rating as d on yes.mID = d.mID and yes.riD = d.rID) as yes1 where yes1.rankk1 = 1 and yes1.rankk2=2;

友情链接