您必须将表rating联接两次到表movie，对于每次联接，必须联接表reviewer。
然后过滤结果，使审阅者不与自己配对，并通过使用distinct和min()和max()函数确保每一对不重复：

select distinct
  min(v1.name, v2.name) reviewer1,
  max(v1.name, v2.name) reviewer2
from movie m
inner join rating r1 on r1.mid = m.mid
inner join rating r2 on r2.mid = m.mid
inner join reviewer v1 on v1.rid = r1.rid
inner join reviewer v2 on v2.rid = r2.rid
where v1.rid <> v2.rid
order by reviewer1, reviewer2

我会从评级时的自我加入开始，然后引入以下名称：

select distinct rv1.name, rv2.name
from rating r1 join
     rating r2
     on r1.mid = r2.mid join
     reviewer rv1
     on rv1.rid = r1.rid join
     reviewer rv2
     on rv2.rid = r2.rid and rv1.name < rv2.name;

您的查询实际上非常相似。我认为主要的问题是select distinct和按名称排序，而不是id。

SELECT DISTINCT MIN(NAME),MAX(NAME) 
FROM (SELECT MID,NAME 
      FROM RATING R,REVIEWER R1 
      WHERE R.RID=R1.RID 
      GROUP BY MID,R.RID 
      ORDER BY MID,NAME)
GROUP BY MID 
ORDER BY MIN(NAME)

正确答案

select distinct rv1.name,rv2.name
from rating r1 
join rating r2 on r1.mid = r2.mid 
join reviewer rv1 on rv1.rID= r1.rID 
join reviewer rv2 on rv2.rID = r2.rID and rv1.name < rv2.name
order by rv1.name,rv2.name;

以下是我的解决方案：

select rev1.*, rev2.*
from Rating rat1
JOIN Rating rat2 on rat2.mID = rat2.mID AND rat1.rID < rat2.rID
JOIN Reviers rev1 ON rev1.rID = rat1.rID
JOIN Reviers rev2 ON rev2.rID = rat2.rID
order by rev1.name, rev2.name

避免使用DISTINCT总是好的。使用ID连接表总是很好的，在此之后，使用一些条件进行排序，比如名称

SELECT rev.name, rev2.name
FROM ( SELECT distinct rID, mID
       FROM   RATING  as r )          as t1
INNER JOIN (  SELECT distinct rID, mID
              FROM   RATING  as r )   as t2    On t1.MID = t2.MID
INNER JOIN Reviewer  as rev   ON t1.RID = rev.RID
INNER JOIN Reviewer  as rev2  ON t2.RID = rev2.RID
WHERE t1.RID <> t2.RID
  AND t1.RID < t2.RID 
GROUP by rev.name, rev2.name
ORDER by 1, 2

Select Distinct Min(name) as Mn, Max(name) as Mx 
From Rating Join Reviewer Using(rID)
Group by mID
Order by Mn

select r1.name n1, r2.name n2 from (
    select distinct a1.rid rid1, a2.rid rid2 from rating a1 inner join rating a2 on a1.mid = a2.mid 
    and not a1.rid = a2.rid
)r 
inner join reviewer r1 on r.rid1 = r1.rid
inner join reviewer r2 on r.rid2 = r2.rid
where r1.name < r2.name order by r1.name

select DISTINCT min(name) as rv1, max(name) rv2
from Rating r1, Reviewer
WHERE (SELECT COUNT(*) from Rating r2 WHERE r1.mID = r2.mID) > 1 and r1.rID = Reviewer.rID
GROUP by r1.mID
ORDER by rv1