Moodle SQL赋值结果/状态

Question

我从Moodle SQL脚本获得了这段SQL代码，如何使结果包含所有项目名，不管它是否有分数？prefix_grade_items表包含分数prefix_grade_items表，项目名不确定是哪个供应商，这些脚本在Moodle开源系统中使用

SELECT u.firstname AS 'Name' , u.lastname AS 'Surname', c.fullname AS 'Course', cc.name AS 'Category', 
CASE WHEN gi.itemtype = 'Course'    
THEN c.fullname + ' Course Total'  
ELSE gi.itemname 
END AS 'Item Name', ROUND(gg.finalgrade / gg.rawgrademax * 100 ,2) AS Percentage

FROM prefix_course AS c 
JOIN prefix_context AS ctx ON c.id = ctx.instanceid 
JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id 
JOIN prefix_user AS u ON u.id = ra.userid 
JOIN prefix_grade_grades AS gg ON gg.userid = u.id 
JOIN prefix_grade_items AS gi ON gi.id = gg.itemid 
JOIN prefix_course_categories AS cc ON cc.id = c.category 
WHERE gi.courseid = c.id AND gi.itemname != 'Attendance' AND gi.itemmodule != 'scorm'
ORDER BY `Name` ASC

Answer 1

您可能只需为grade_grades和grade_items切换连接，然后在grade_grades上进行左连接。我还会将WHERE条件移到gi联接：

JOIN prefix_grade_items AS gi ON gi.courseid = c.id AND gi.itemname != 'Attendance' AND gi.itemmodule != 'scorm'
LEFT JOIN prefix_grade_grades AS gg ON gg.userid = u.id AND gg.itemid = gi.id

Moodle可以使用Mysql、Postgresql、MariaDB、Server和Oracle，因此查询是通用的。这里有一些跨数据库兼容查询可用的函数：函数