mysql - SQL数据库调查数据的平均评分

我一直在试图返回一个组织者的平均评分，这个评分基于一个包含调查数据的表（针对一列）的评分。

Table: Workshops
Cols: id[pk] | title | description | survey_id[fk] | organizer_id[fk]

Table: Organizers
Cols: organizer_id[pk] | organizer_name | organizer_email | organizer_rating

Table: Surveys
Cols: survey_id[pk] | survey_desc | survey

用户响应表如下：

Table: SurveyUserResponse
Cols: s_u_r_id[pk] | username | survey_id[fk] | answer_1 | answer_2 | answer_3

答案3本质上是演讲者的评价。我试图选择答案3的平均评分，并根据组织者ID将其加入研讨会，但它没有返回组织者的正确平均值。
这让我很困惑，我不确定如何将评分纳入评分表的组织者评分栏。
任何帮助都将不胜感激。
编辑：
谢谢你的建议。根据您的建议，这是一个示例记录：

Table: Workshops
id | title | description | survey_id | organizer_id
---------------------------------------------------
1  | ws01  | on pottery  | 1         | 1

Table: Organizers
organizer_id | organizer_name | organizer_email | organizer_rating
-----------------------------------------------------------------------
1            | Ray Dion       | [email protected] | <trying to get result here>

Table : Surveys
survey_id | survey_desc | survey
---------------------------------
1         | ws01 survey | test

Table: SurveyUserResponse
s_u_r_id | username  | survey_id | answer_1 | answer_2 | answer_3
-----------------------------------------------------------------
114      |  joe21331 | 1         | 4        | 5        | 3

这就是我到目前为止为了测试是否返回了正确的数据集而提出的：

 SELECT     Organizers.organizer_id, Organizers.organizer_name,
       AVG(survey_user_response.answer_value_3) AS organizer_rating
 FROM Organizers, survey_user_response
 INNER JOIN Workshops organizer_id
 ON Workshops.organizer_id = Organizers.organizer_id
 ORDER BY organizer_rating DESC

最佳答案

在MySQL中：

SELECT  o.*,
        AVG(answer_3)
FROM    surveyUserResponse sur
JOIN    workshops w
USING   (survey_id)
JOIN    organizers o
USING   (organizer_id)
GROUP BY
        organizer_id

在SQL Server中：

SELECT  *
FROM    organizers o
CROSS APPLY
        (
        SELECT  AVG(answer_3)
        FROM    workshops w
        JOIN    surveyUserResponse sur
        ON      sur.survey_id = w.survey_id
        WHERE   w.organizer_id = o.organizer_id
        ) q (rating)

关于mysql - SQL数据库调查数据的平均评分，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/36550777/