如果Roll No不在MySQL的评级列表中，则将平均评级视为零

Question

考虑以下模式：

Student (RollNo int Not Null, Name varchar(20) Not Null, YearOfAdmission int Not Null,
PRIMARY KEY(RollNo))

Friend(OwnRoll int Not Null, FriendRoll int Not Null,
PRIMARY KEY(OwnRoll, FriendRoll),
FOREIGN KEY fk_std1(OwnRoll) REFERENCES Student(RollNo),
FOREIGN KEY fk_std2(FriendRoll) REFERENCES Student(RollNo))

Movie(MID int Not Null, Title varchar(30) Not Null, YearOfRelease int Not Null, DirectorName
varchar(20) Null,
PRIMARY KEY(MID)) [Assume all director names are unique. However, same director can direct
many movies]

Rating(RollNo int Not Null, MID int Not Null, RatingDate date Not Null, Rating int Not Null,
PRIMARY KEY(RollNo, MID, RatingDate),
FOREIGN KEY fk_std4(RollNo) REFERENCES Student(RollNo),
FOREIGN KEY fk_mov2(MID) REFERENCES Movie(MID));

现在Ques如下：

列出学生对所有电影的平均评分(包括多个评分实例不同日期的电影(的评分低于他/她的朋友对这些电影的平均评分(包括在不同日期对电影进行评级的多个实例(。(输出格式：RollNo1，AverageRating1、RollNo2、AverageRating 2(

其中一个可能的答案是

Select x.OwnRoll as RollNo1, l1.average as AverageRating1,
x.FriendRoll as RollNo2, l2.average as AverageRating2
from 
(
Select * from Friend 
union 
(
Select f.FriendRoll, f.OwnRoll from Friend as f
)
order by OwnRoll
) as x,
(
Select r.Rollno, avg(r.Rating) as average
from Rating as r 
group by r.Rollno
) as l1,
(
Select r.Rollno, avg(r.Rating) as average
from Rating as r 
group by r.Rollno
) as l2
where l1.Rollno = x.OwnRoll and l2.Rollno = x.FriendRoll
and l1.average > l2.average ;

但这个版本没有考虑从未给电影打分的朋友，因此他们的平均评分为0

提前感谢您对问题及其答案的任何更新。

Answer 1

进行此litte更新，以在不存在Roll No的情况下将评分平均值捕获为0。

Select distinct r.Rollno, avg(r.Rating) as average
from Rating as r 
group by r.Rollno
union
(
Select RollNo, 0 as average
from Student 
where Rollno not in (Select RollNo from Rating)
)

你甚至可以关注Coalesce更多关于这里提到的

类似的问题张贴在这里

最终答案是

Select x.OwnRoll as RollNo1, l1.average as AverageRating1,
x.FriendRoll as RollNo2, l2.average as AverageRating2
from 
(
Select * from Friend 
union (Select f.FriendRoll, f.OwnRoll from Friend as f)
order by OwnRoll
) as x,
(
Select distinct r.Rollno, avg(r.Rating) as average
from Rating as r 
group by r.Rollno
union
(
Select RollNo, 0 as average
from Student 
where Rollno not in 
(Select RollNo from Rating))
) as l1,
(
Select distinct r.Rollno, avg(r.Rating) as average
from Rating as r 
group by r.Rollno
union
(
Select RollNo, 0 as average
from Student 
where Rollno not in 
(Select RollNo from Rating))
) as l2
where l1.Rollno = x.OwnRoll and l2.Rollno = x.FriendRoll
and l1.average > l2.average 

试着一步一步地理解它！！否则你会迷路：(