将2个SQL查询与公共字段和AVGs合并

Question

我的SQL在两年没有研究过之后有点生疏，所以我找不到一个方法来做这个。

我需要从Moodle数据库中获得一些数据，也就是一些反馈测试的平均值，学生在这些测试中对老师进行评分。

这个SQL查询：

SELECT mdl_course.id, mdl_user.username FROM mdl_course
INNER JOIN mdl_context ON mdl_context.instanceid = mdl_course.id
INNER JOIN mdl_role_assignments ON mdl_context.id = mdl_role_assignments.contextid
INNER JOIN mdl_role ON mdl_role.id = mdl_role_assignments.roleid
INNER JOIN mdl_user ON mdl_user.id = mdl_role_assignments.userid
WHERE mdl_role.id = 3

返回这样的东西，一个表，每门课和它的老师分配。

COURSE_ID TEACHER   
2          john
3          mary
4          john

现在，我的第二个SQL如下：

SELECT mdl_feedback.course, AVG(mdl_feedback_value.value) as average
FROM mdl_feedback_value 
INNER JOIN mdl_feedback_item ON mdl_feedback_value.item = mdl_feedback_item.id 
INNER JOIN mdl_feedback ON mdl_feedback.id = mdl_feedback_item.feedback
INNER JOIN mdl_feedback_completed ON mdl_feedback.id = mdl_feedback_completed.feedback
INNER JOIN mdl_user ON mdl_feedback_completed.userid = mdl_user.id
GROUP BY mdl_feedback.course

COURSE    AVERAGE   
2          3.5
3           3
4          3.25

我想要的是将这2个SQL查询组合成一个这样的查询，使用COURSE/COURSE_ID作为键

TEACHER    AVERAGE
john         3,375   <--- avg of 3,5 and 3,25 from each of john's courses
mary         3       <--- she has just one course so no math here

我不知道如何做到这一点，所以我非常感谢一些帮助:)正如我所说的，我已经有一段时间没有使用SQL了，所以我不热衷于那些连接瘦，也许我必须使用它们，因为我在这里使用了它们。

我使用的是MySQL 5.5.33，虽然这与Moodle有关，但答案并不是真正以Moodle为中心的，因为这里唯一重要的是两个查询的输出是什么表。

谢谢

Answer 1

如果您接受每个子查询，然后尝试加入它们，那么它应该可以工作。我添加了第三个字段，以帮助您了解如何创建分数的详细信息。

SELECT username, AVG(average) AS average
  , GROUP_CONCAT(CONCAT('Course: ', teachers.course_id,' with score ', COALESCE(average,'No Score Found'))) AS detail
FROM (
    SELECT mdl_course.id AS course_id, mdl_user.username AS username
    FROM mdl_course
    INNER JOIN mdl_context ON mdl_context.instanceid = mdl_course.id
    INNER JOIN mdl_role_assignments ON mdl_context.id = mdl_role_assignments.contextid
    INNER JOIN mdl_role ON mdl_role.id = mdl_role_assignments.roleid
    INNER JOIN mdl_user ON mdl_user.id = mdl_role_assignments.userid
    WHERE mdl_role.id = 3 ) AS teachers
LEFT JOIN (
    SELECT mdl_feedback.course AS course_id, AVG(mdl_feedback_value.value) as average
    FROM mdl_feedback_value 
    INNER JOIN mdl_feedback_item ON mdl_feedback_value.item = mdl_feedback_item.id 
    INNER JOIN mdl_feedback ON mdl_feedback.id = mdl_feedback_item.feedback
    INNER JOIN mdl_feedback_completed ON mdl_feedback.id = mdl_feedback_completed.feedback
    INNER JOIN mdl_user ON mdl_feedback_completed.userid = mdl_user.id
    GROUP BY mdl_feedback.course) AS scores
  ON teachers.course_id = scores.course_id
GROUP BY username

Answer 2

我试图使用临时表来存储两个不同的查询结果集，然后查询临时表以获得教师的平均值。希望这能行。

CREATE TABLE #TEMP
(
COURSE_ID int,
TEACHER varchar(100),
COURSE int,
AVERAGE int
)
--- Inserting the course_id and Teahcer data----

INSERT INTO #TEMP
(
COURSE_ID,
TEACHER
)
SELECT mdl_course.id, mdl_user.username FROM mdl_course
INNER JOIN mdl_context ON mdl_context.instanceid = mdl_course.id
INNER JOIN mdl_role_assignments ON mdl_context.id = mdl_role_assignments.contextid
INNER JOIN mdl_role ON mdl_role.id = mdl_role_assignments.roleid
INNER JOIN mdl_user ON mdl_user.id = mdl_role_assignments.userid
WHERE mdl_role.id = 3

-- Inserting the course and average data---
INSERT INTO #TEMP
(
COURSE,
AVERAGE
)
SELECT mdl_feedback.course, AVG(mdl_feedback_value.value) as average
FROM mdl_feedback_value 
INNER JOIN mdl_feedback_item ON mdl_feedback_value.item = mdl_feedback_item.id 
INNER JOIN mdl_feedback ON mdl_feedback.id = mdl_feedback_item.feedback
INNER JOIN mdl_feedback_completed ON mdl_feedback.id = mdl_feedback_completed.feedback
INNER JOIN mdl_user ON mdl_feedback_completed.userid = mdl_user.id
GROUP BY mdl_feedback.course


--- Querying the # temp table for the average and teacher---

SELECT TEACHER, AVG(AVERAGE)
FROM #TEMP
GROUP BY TEACHER

Answer 3

您可以尝试使用以下方法连接这两个查询

INNER JOIN mdl_course ON mdl_feedback.course = mdl_course.id

请查看此组合查询是否有效：

SELECT mdl_user.username, AVG(mdl_feedback_value.value) as average
FROM mdl_feedback_value 
INNER JOIN mdl_feedback_item ON mdl_feedback_value.item = mdl_feedback_item.id 
INNER JOIN mdl_feedback ON mdl_feedback.id = mdl_feedback_item.feedback
INNER JOIN mdl_feedback_completed ON mdl_feedback.id = mdl_feedback_completed.feedback
INNER JOIN mdl_user ON mdl_feedback_completed.userid = mdl_user.id
INNER JOIN mdl_course ON mdl_feedback.course = mdl_course.id
INNER JOIN mdl_context ON mdl_context.instanceid = mdl_course.id
INNER JOIN mdl_role_assignments ON mdl_context.id = mdl_role_assignments.contextid
INNER JOIN mdl_role ON mdl_role.id = mdl_role_assignments.roleid
INNER JOIN mdl_user ON mdl_user.id = mdl_role_assignments.userid
WHERE mdl_role.id = 3
GROUP BY mdl_user.username;