sql - Moodle:用于列出所有用户的 SQL(带有自定义用户配置文件字段)

Question

我一直在寻找合适的 SQL 查询来检索角色为 student 的所有用户的详细信息。该信息将包括一些自定义用户配置文件字段的数据。

例如，我创建了 2 个用户配置文件字段，简称为 Department 和 PositionRank。我已设法使用以下 SQL 检索列表:

SELECT DISTINCT u.id AS userid, department_data.data AS department, rank_data.data AS rank    
FROM mdl_user u
JOIN mdl_role_assignments ra ON ra.userid = u.id
JOIN mdl_role r ON r.id = ra.roleid AND r.shortname =  'student'    
JOIN mdl_user_info_data department_data ON department_data.userid = u.id
JOIN mdl_user_info_field department_field ON department_data.fieldid = department_field.id AND department_field.shortname = 'Department'    
JOIN mdl_user_info_data rank_data ON rank_data.userid = u.id
JOIN mdl_user_info_field rank_field ON rank_data.fieldid = rank_field.id AND rank_field.shortname = 'PositionRank'

但我觉得应该有更好的方法。有人可以建议吗？谢谢。

Answer 1

您可以在列中使用 GROUP BY 和 CASE..ELSE，因此您只有一个连接到数据表

SELECT u.id AS userid,
        MAX(CASE WHEN f.shortname = 'Department' THEN d.data ELSE '' END) AS department,
        MAX(CASE WHEN f.shortname = 'PositionRank' THEN d.data ELSE '' END) AS rank
FROM mdl_user u
JOIN mdl_role_assignments ra ON ra.userid = u.id
JOIN mdl_role r ON r.id = ra.roleid AND r.shortname =  'student'
JOIN mdl_user_info_data d ON d.userid = u.id
JOIN mdl_user_info_field f ON d.fieldid = f.id
WHERE f.shortname IN ('Department', 'PositionRank')
GROUP BY u.id