我有以下SQL测试表; 在此处查看 SQLFiddle
> SELECT * FROM `Movie`;
+-----+-------------------------+------+------------------+
| mID | title | year | director |
+-----+-------------------------+------+------------------+
| 101 | Gone with the Wind | 1939 | Victor Fleming |
| 102 | Star Wars | 1977 | George Lucas |
| 103 | The Sound of Music | 1965 | Robert Wise |
| 104 | E.T. | 1982 | Steven Spielberg |
| 105 | Titanic | 1997 | James Cameron |
| 106 | Snow White | 1937 | <null> |
| 107 | Avatar | 2009 | James Cameron |
| 108 | Raiders of the Lost Ark | 1981 | Steven Spielberg |
+-----+-------------------------+------+------------------+
> SELECT * FROM Rating;
+-----+-----+-------+------------+
| rID | mID | stars | ratingDate |
+-----+-----+-------+------------+
| 201 | 101 | 2 | 2012-01-22 |
| 201 | 101 | 4 | 2013-01-27 |
| 202 | 106 | 4 | <null> |
| 203 | 103 | 2 | 2008-01-20 |
| 203 | 108 | 4 | 2002-01-12 |
| 203 | 108 | 2 | 2009-01-30 |
| 204 | 101 | 3 | 2010-01-09 |
| 205 | 103 | 3 | 2010-01-27 |
| 205 | 104 | 2 | 2010-01-22 |
| 205 | 108 | 4 | <null> |
| 206 | 107 | 3 | 2013-01-15 |
| 206 | 106 | 5 | 2014-01-19 |
| 207 | 107 | 5 | 2000-01-20 |
| 208 | 104 | 3 | 1999-01-02 |
+-----+-----+-------+------------+
> SELECT * FROM Reviewer;
+-----+------------------+
| rID | name |
+-----+------------------+
| 201 | Sarah Martinez |
| 202 | Daniel Lewis |
| 203 | Brittany Harris |
| 204 | Mike Anderson |
| 205 | Chris Jackson |
| 206 | Elizabeth Thomas |
| 207 | James Cameron |
| 208 | Ashley White |
+-----+------------------+
我已经解决了所有问题,除了这两个:
1.)对于每部至少有一个评级的电影,找到电影标题和星总数,最高星和给予最高星的人。
我得到了什么:
SELECT m.title, ra.stars, re.name
FROM Movie m
JOIN(
SELECT R.*
FROM Rating R
JOIN(
SELECT mid, MAX(stars) AS Stars
FROM Rating
GROUP BY mid
) D ON R.mid = D.mid AND R.Stars = D.Stars
) Ra ON m.mid = ra.mid
JOIN Reviewer re ON ra.rid = re.rid;
+-------------------------+-------+------------------+
| title | stars | name |
+-------------------------+-------+------------------+
| Gone with the Wind | 4 | Sarah Martinez |
| Raiders of the Lost Ark | 4 | Brittany Harris |
| The Sound of Music | 3 | Chris Jackson |
| Raiders of the Lost Ark | 4 | Chris Jackson |
| Snow White | 5 | Elizabeth Thomas |
| Avatar | 5 | James Cameron |
| E.T. | 3 | Ashley White |
+-------------------------+-------+------------------+
缺少什么:我找不到将每部电影SUM(stars)
添加到表中的方法。
2.) 对于同一评论者对同一部电影进行两次评分并在第二次给予更高评分的所有情况,请返回评论者的姓名和电影标题。
到目前为止我得到了什么:
SELECT title, name
FROM Movie m
JOIN Rating ra ON m.mid = ra.mid
JOIN Reviewer re ON ra.rid = re.rid
GROUP BY title, name
HAVING COUNT(*) > 1;
+-------------------------+-----------------+
| title | name |
+-------------------------+-----------------+
| Gone with the Wind | Sarah Martinez |
| Raiders of the Lost Ark | Brittany Harris |
+-------------------------+-----------------+
缺少什么: 我有所有被同一位评论家评为两次的电影,但是我不知道如何过滤案例,因为最新的评论比以前的评论有更多的星星。
如果有人能在这里指出我正确的方向,我将不胜感激。堆栈溢出今天对我非常有帮助:)
编辑:添加我的尝试和缺少的内容。
这应该给你你想要的数字 1,对于数字 2,我需要 sqlfiddle 中的数据来玩。 与此同时,我建议看看lag
尽管first
和last
的某种组合可能会给你你想要的东西。 *请注意,这不会给您确切的答案,它仅供参考。
select mov.title, sum(rat.stars), max(rat.stars), rev.name
from Movie mov,
Rating rat,
Reviewer rev
where mov.mid = rat.mid
and rat.rid = rev.rid
group by mov.title;