mysql - 如何获取每个用户的评分？

Question

我在数据库中有这样一种关系。

我编写了这样的 SQL 代码来获取用户的平均评分列表。

SELECT
  c.id,l.login,
    AVG(f.mark) AS AVGRating
  FROM logininfo l
 INNER JOIN person p
   ON l.id = p.info_id
 JOIN candidate c
    ON p.id = c.id
  JOIN feedback f
GROUP BY c.id
ORDER BY AVGRating ASC; 

问题是我对所有用户都得到相同的评分。

Answer 1

您错过了反馈表的连接条件:

SELECT
  c.id,l.login,
  AVG(f.mark) AS AVGRating
FROM logininfo l
  JOIN person p
    ON l.id = p.info_id
  JOIN candidate c
    ON p.id = c.id
  JOIN feedback f
    ON c.id = f.candidat_id -- < -- this one
GROUP BY c.id
ORDER BY AVGRating ASC; 

顺便说一句，INNER JOIN 和 JOIN 是相同的。