mysql

mwngjboj 于 2021-06-25 发布在 Mysql

关注(0)|答案(4)|浏览(279)

我有三个表包含电影，评级和评论员数据。我的数据连接如下所示：

mysql root@localhost:foo> SELECT m.title, ra.stars, re.name
                       -> FROM Movie m
                       -> JOIN Rating ra ON m.mid = ra.mid
                       -> JOIN `Reviewer` re ON ra.rid = re.rid
                       -> ORDER BY m.title, ra.stars DESC;
+-------------------------+-------+------------------+
| title                   | stars | name             |
+-------------------------+-------+------------------+
| Avatar                  | 5     | James Cameron    |
| Avatar                  | 3     | Elizabeth Thomas |
| E.T.                    | 3     | Ashley White     |
| E.T.                    | 2     | Chris Jackson    |
| Gone with the Wind      | 4     | Sarah Martinez   |
| Gone with the Wind      | 3     | Mike Anderson    |
| Gone with the Wind      | 2     | Sarah Martinez   |
| Raiders of the Lost Ark | 4     | Brittany Harris  |
| Raiders of the Lost Ark | 4     | Chris Jackson    |
| Raiders of the Lost Ark | 2     | Brittany Harris  |
| Snow White              | 5     | Elizabeth Thomas |
| Snow White              | 4     | Daniel Lewis     |
| The Sound of Music      | 3     | Chris Jackson    |
| The Sound of Music      | 2     | Brittany Harris  |
+-------------------------+-------+------------------+

我试图达到的目的是将上表按标题分组，并获得给每部电影最高评分的评论员的姓名。结果如下所示。

+-------------------------+-------+------------------+
| title                   | stars | name             |
+-------------------------+-------+------------------+
| Avatar                  | 5     | James Cameron    |
| E.T.                    | 3     | Ashley White     |
| Gone with the Wind      | 4     | Sarah Martinez   |
| Raiders of the Lost Ark | 4     | Brittany Harris  |
| Raiders of the Lost Ark | 4     | Chris Jackson    |
| Snow White              | 5     | Elizabeth Thomas |
| The Sound of Music      | 3     | Chris Jackson    |
+-------------------------+-------+------------------+

不知怎的，我被困在如何做那件事上了。有人能给我指出正确的方向吗？
编辑：我最初认为在stars列中包含另一个聚合函数( MAX(stars) )很琐碎，但我不能让它工作。有什么办法吗？

sql mysql group-by Max

来源：https://stackoverflow.com/questions/49918275/sql-find-row-value-in-other-column-of-groups-max-value-mysql

4条答案

按热度按时间

u4dcyp6a1#

下面的代码将对您有所帮助，

SELECT m.title, ra.stars, re.name
FROM Movie m
JOIN Rating ra ON m.mid = ra.mid
JOIN Reviewer re ON ra.rid = re.rid
WHERE ra.stars = ( SELECT MAX(ra1.stars) FROM Movie m1 JOIN Rating ra1 on m1.mid = ra1.mid WHERE m1.title = m.title) 
ORDER BY m.title, ra.stars DESC;

赞(0）回复(0）举报 2021-06-26

6tqwzwtp2#

SELECT m.title, ra.stars, re.name
       FROM Movie m
      JOIN Rating ra ON m.mid = ra.mid
      JOIN Reviewer re ON ra.rid = re.rid
      JOIN 
 (SELECT m.title, max(ra.stars) as stars
       FROM Movie m
      JOIN Rating ra ON m.mid = ra.mid
      GROUP BY m.title) c
ON c.title=m.title AND ra.stars=c.stars
      ORDER BY m.title, ra.stars DESC

赞(0）回复(0）举报 2021-06-26

wztqucjr3#

SELECT m.title, ra.stars, re.name
FROM Movie m
JOIN (SELECT mid,MAX(stars) AS stars FROM Rating GROUP BY mid) ra 
ON m.mid = ra.mid
JOIN Rating ra1
ON ra1.mid = ra.mid AND ra1.star=ra.star
JOIN Reviewer re 
ON ra1.rid = re.rid
ORDER BY m.title, ra.stars DESC;

赞(0）回复(0）举报 2021-06-25

rekjcdws4#

解决方案1:-从结果集中：

SELECT A.* 
FROM Your_Table A
JOIN(
    SELECT title,MAX(stars) stars
    FROM Your_Table
    GROUP BY title
    )B ON A.title = B.title AND A.stars = B.stars

第二个解决方案：-从你的table

SELECT m.title, ra.stars, re.name
FROM Movie m
JOIN(
    SELECT R.* 
    FROM Rating R
    JOIN(
        SELECT mid
            ,MAX(stars) 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

赞(0）回复(0）举报 2021-06-25

我来回答

mysql

4条答案

相关问题

热门标签

最新问答