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屋!