主页 / dba / 问题 / 33357
Accepted
Roy
Asked: 2013-01-23 11:52:02 +0800 CST

如何创建此 SQL 查询？

  • 772

我正在斯坦福在线参加免费的 DB 课程，坦率地说，其中一个练习给我带来了一些麻烦。我有一种感觉，这应该非常简单，所以对于 DBA，我显然不太擅长 SQL。

我们正在使用简化的电影评级方案。

对于同一评论者对同一部电影两次评分并第二次给予更高评分的所有情况，返回评论者的姓名和电影的标题。

这是架构：

Movie ( mID, title, year, director ) 
Reviewer ( rID, name ) 
Rating ( rID, mID, stars, ratingDate )

我该怎么办？

query

4 个回答

  1. Best Answer

    您可以根据此类条件自行加入表（至少，我可以在 SQL Server 中执行此操作）：

    SELECT
     r.name,
     m.title
FROM
    rating rt
    JOIN movie m ON (rt.mid = m.mid)
    JOIN reviewer r on (rt.rid = r.rid)
    JOIN rating rt2 on (rt.mid = rt2.mid and rt.rid = rt2.rid)
WHERE
    rt.ratingDate > rt2.ratingDate
    AND rt.stars >  rt2.stars;

    请注意，我们正在将评级归还给自身。这样，我们可以使用 WHERE 子句来查找rating日期和星号都大于rating.

    您可以在此处查看我在 SQL Fiddle 中构建的完整示例。

    如果您想添加评论者对电影进行了这 2 条评论并且不再添加的条件，您可以添加：

        AND NOT EXISTS                                  --- not exists
        ( SELECT *
          FROM rating rt3                           --- another rating
          WHERE rt3.mid = m.mid                     --- for same movie
            AND rt3.rid = r.rid                     --- by same reviewer
            AND rt3.ratingDate <> rt2.ratingDate    --- on different date
            AND rt3.ratingDate <> rt1.ratingDate    --- than the first 2 reviews
        )

    这假定该Rating表具有 (rID, mID, ratingDate)作为主键。

    • 4

  2. 这是使用 SQL Fiddle 中内置的表 Miracle173 的分析解决方案。该分析减轻了查询 Ratings 表的需要两次三次。

    SELECT re.Name, mo.Title FROM
(
   SELECT rID, mID, count(*) OVER (PARTITION BY rID, mID) MovieCount, stars
      , Lag(Stars) OVER (PARTITION BY rID, mID ORDER BY ratingDate) LagStars
   FROM Rating
) ra
JOIN Movie mo ON mo.mID = ra.mID
JOIN Reviewer re ON re.rID = ra.rID
WHERE MovieCount = 2 AND Stars > LagStars;

    http://sqlfiddle.com/#!4/6957e/16

    • 4 
  3. 选择姓名、职务
从 Rating Rating1 加入 Rating Rating2 on(
Rating1.mID=Rating2.mID
和 Rating1.rID=Rating2.rID
和 Rating1.ratingDate Rating1.stars)
加入电影 (Movie.mID=Rating1.mID)
加入审稿人 (Reviewer.rID=Rating1.rID)
和 (Rating1.rID, Rating1.mID) 在 (select rID,mID
从评级
按 rID、mID 分组
计数（*）=2）
;

    您可以在http://sqlfiddle.com/#!4/6957e/7查看。

    它只会考虑一个评论家的电影，他的评分正好是两次

    • 3

  4. 结合先前答案中的部分的解决方案如下：

    select
  name,title
from 
  rating rt1
  join rating rt2 on (rt1.rID = rt2.rID and rt1.mID = rt2.mID)
  join reviewer on (rt1.rID = reviewer.rID)
  join movie on (rt1.mID = movie.mID)
where
  rt1.ratingdate < rt2.ratingdate 
  and rt1.stars < rt2.stars
  and rt1.rID in (
    select rID
    from Rating r1
    group by r1.rID,r1.mID
    having count(*)=2);
    • 3

相关问题