时间戳

首页 > 解决方案 > MYSQL：将 ID 表转换为名称表

mysql - MYSQL：将 ID 表转换为名称表

问题描述

有人可以帮我做这个 SQL 练习吗？

电影（mID，片名，年份，导演） 定义：有一部电影，ID号为mID，片名，上映年份，导演。

Reviewer ( rID, name ) 定义：ID号为rID的reviewer有一定的名字。

评分 ( rID, mID, stars, ratingDate ) 定义：评论者 rID 在某个 ratingDate 给电影 mID 的星级评分 (1-5)。

对于所有评论者对，使得两位评论者都对同一部电影进行评分，返回两位评论者的姓名。消除重复，不要将审阅者与他们自己配对，并且每对只包含一次。对于每一对，按字母顺序返回该对中的名称。

我编写了以下查询来获取成对的审阅者 ID，但很难找到一种有效的方法来在表中获取两个审阅者的姓名：

select distinct r1.rID as rID1, r2.rID as rID2
from Rating r1, Rating r2
where r1.rID < r2.rID
and r1.mID = r2.mID;

标签： mysql

解决方案

放弃连接操作的旧式逗号语法，改用JOIN关键字。

我们将需要对审阅者表的两个引用，一个用于行中的每个审阅者。

 SELECT n1.name    AS r1_name
      , n2.name    AS r2_name

   FROM rating r1

   JOIN rating r2
     ON r1.mid  = r2.mid     -- reviewed the same movie 
    AND r1.rid <> r2.rid     -- don't pair reviewer with themselves (different reviewer)

   JOIN reviewer n1 
     ON n1.rid = r1.rid         

   JOIN reviewer n2 
     ON n2.rid = r2.rid   

  WHERE n1.name < n2.name    -- names in the pair in alphabetical order 

  GROUP
     BY n1.rid
      , n2.rid               -- include each pair of reviewers only once (eliminate duplicates) 

  ORDER
     BY n1.name
      , n2.name              -- result set more deterministic

“......最好采用一致的约定，例如始终使用小写名称创建和引用数据库和表。建议使用此约定以实现最大的可移植性和易用性。”

MySQL 参考手册

https://dev.mysql.com/doc/refman/5.7/en/identifier-case-sensitive.html

编辑

更改GROUP BY为审阅者rid而不是name。

这将只返回每个“一对审阅者”一次。如果两个或多个审阅者具有相同的姓名，则查询可能会返回具有相同名称对的行，但这些行将代表不同的审阅者对。

推荐阅读