我需要使用子查詢（突出顯示）計算 Moodle 中每個作業（a.name）的平均成績（gg.finalgrade）。我想不通
SELECT
u.username,
u.firstname,
u.lastname,
u.id,
c.fullname AS "Course",
a.name,
IFNULL(ROUND(gg.finalgrade / gg.rawgrademax * 100 ,0),0) as 'Percentage',
**(
SELECT SUM(gg.finalgrade) / a.name
FROM prefix_assign a
JOIN prefix_course c ON c.id = a.course
JOIN prefix_grade_items gi on gi.courseid = c.id
JOIN prefix_grade_grades gg ON gg.itemid = gi.id
JOIN prefix_user u ON u.id = gg.userid
) AS 'Avg'**
FROM prefix_user u
JOIN prefix_role_assignments ra ON ra.userid = u.id
JOIN prefix_context ctx ON ctx.id = ra.contextid
JOIN prefix_course c ON c.id = ctx.instanceid
JOIN prefix_course_modules cm ON cm.course = c.id
JOIN prefix_modules m ON m.id = cm.module AND m.name = 'assign'
JOIN prefix_assign a ON a.id = cm.instance AND a.course = c.id
JOIN prefix_grade_items gi on gi.courseid = c.id AND gi.itemmodule = m.name AND gi.iteminstance = a.id
LEFT JOIN prefix_grade_grades gg ON gg.itemid = gi.id AND gg.userid = u.id
GROUP BY a.id, u.id
uj5u.com熱心網友回復：
嘗試使用OVER子句：
SELECT SUM(gg.finalgrade) OVER(partition by a.name)/ count (*) OVER (partition by a.name)
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/504988.html