SQL-比较两行两列 [英] SQL - Comparing two rows and two columns

问题描述

我正在研究SQL，似乎无法找到答案。

I'm studying SQL and can't seem to find an answer to this exercise.

锻炼：对于所有由同一位审稿人进行评分的情况同一部电影两次，并第二次给予较高评价，返回评论者的姓名和电影标题。

我不知道如何比较两行，然后获得更高的评级。

I don't know how to compare 2 rows and then get the higher rating.

表的模式为：

• 电影（mID，标题，年份，导演）
  英语：有一部ID号为mID，标题，发行年
  和导演的电影。


• 审阅者（rID，名称）
  英语：ID编号为rID的审阅者具有特定名称。


• 评分（rID，mID，星星，ratingDate）
  英语：审阅者rID在特定的ratingDate上给电影mIDa评分为星级（1-5）。*

在论坛的此处进行搜索，到目前为止我已经了解到了：

Researching here in the forum I've got as far as to this point:

select *
from rating a
join Reviewer rv on rv.rid = a.rid
where 1 < (select COUNT(*) from rating b
            where b.rid = a.rid and b.mid = a.mid)

我也很高兴收到代码的解释。因为甚至上面的代码也让我感到非常困惑。

I'd be glad to be given also an explanation of the code. Since even the code above is making me really confused.

/* Create the schema for our tables */
create table Movie(mID int, title text, year int, director text);
create table Reviewer(rID int, name text);
create table Rating(rID int, mID int, stars int, ratingDate date);

/* Populate the tables with our data */
insert into Movie values(101, 'Gone with the Wind', 1939, 'Victor Fleming');
insert into Movie values(102, 'Star Wars', 1977, 'George Lucas');
insert into Movie values(103, 'The Sound of Music', 1965, 'Robert Wise');
insert into Movie values(104, 'E.T.', 1982, 'Steven Spielberg');
insert into Movie values(105, 'Titanic', 1997, 'James Cameron');
insert into Movie values(106, 'Snow White', 1937, null);
insert into Movie values(107, 'Avatar', 2009, 'James Cameron');
insert into Movie values(108, 'Raiders of the Lost Ark', 1981, 'Steven Spielberg');

insert into Reviewer values(201, 'Sarah Martinez');
insert into Reviewer values(202, 'Daniel Lewis');
insert into Reviewer values(203, 'Brittany Harris');
insert into Reviewer values(204, 'Mike Anderson');
insert into Reviewer values(205, 'Chris Jackson');
insert into Reviewer values(206, 'Elizabeth Thomas');
insert into Reviewer values(207, 'James Cameron');
insert into Reviewer values(208, 'Ashley White');

insert into Rating values(201, 101, 2, '2011-01-22');
insert into Rating values(201, 101, 4, '2011-01-27');
insert into Rating values(202, 106, 4, null);
insert into Rating values(203, 103, 2, '2011-01-20');
insert into Rating values(203, 108, 4, '2011-01-12');
insert into Rating values(203, 108, 2, '2011-01-30');
insert into Rating values(204, 101, 3, '2011-01-09');
insert into Rating values(205, 103, 3, '2011-01-27');
insert into Rating values(205, 104, 2, '2011-01-22');
insert into Rating values(205, 108, 4, null);
insert into Rating values(206, 107, 3, '2011-01-15');
insert into Rating values(206, 106, 5, '2011-01-19');
insert into Rating values(207, 107, 5, '2011-01-20');
insert into Rating values(208, 104, 3, '2011-01-02');

推荐答案

类似的方法应该可以工作（其他方法，

something like that should work (they are other ways, too)

SELECT rev.name, m.title
FROM Reviewer rev
INNER JOIN Rating r1 on r1.rID = rev.rID
INNER JOIN Rating r2 on r2.rID = rev.rID and r2.mID = r1.mID
INNER JOIN Movie m on m.mID = r1.mID
WHERE r2.ratingDate > r1.ratingDate and r2.stars > r1.stars 

，或者在这种情况下，您可以全部加入连接（而不是WHERE子句）

or you can do all in join (instead of WHERE clause) in this case

SELECT rev.name, m.title
FROM Reviewer rev
INNER JOIN Rating r1 on r1.rID = rev.rID
INNER JOIN Rating r2 
  on r2.rID = rev.rID 
  and r2.mID = r1.mID
  and r2.ratingDate > r1.ratingDate
  and r2.stars > r1.stars
INNER JOIN Movie m on m.mID = r1.mID

SqlFiddle （带有示例数据）

说明：我想您知道JOIN语法，所以

Explanation : I suppose you know the JOIN syntax, so

诀窍是两次加入Rating。
然后，WHERE部分会检查是否有一条线，其中一个评分（来自同一电影的同一位审稿人）具有更大的ratingDate和更多的星级。

The trick is to join Rating two times. Then the WHERE part checks if there's exist a line where one of the rating (from same reviewer on same movie) has a bigger ratingDate and more stars. Which checks : "gave it a higher rating the second time".

然后我们只按评论者姓名和电影标题分组（如果我们有3条评论，这部分是避免重复） ，第二颗星比第一颗星多，第三颗比第二颗星多）：使用示例数据，不需要使用GROUP BY，但是...

Then we just group by reviewerName and movie title (this part is to avoid duplicates if we have 3 reviews, the second having more stars than the first, and the third more than the second) : with your sample datas, the GROUP BY is not needed, but...

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