mysql 90天以内_mysql – Moodle SQL查询,用于检索在过去90天内注册到课程的所有用户...

嘿家伙可以有人帮助我,我需要一个SQL查询,这将给我所有注册到过去90天未被暂停的课程的用户.

我做了一些搜索,发现了这个：

SELECT DISTINCT u.id AS userid, c.id AS courseid

FROM mdl_user u

JOIN mdl_user_enrolments ue ON ue.userid = u.id

JOIN mdl_enrol e ON e.id = ue.enrolid

JOIN mdl_role_assignments ra ON ra.userid = u.id

JOIN mdl_context ct ON ct.id = ra.contextid AND ct.contextlevel = 50

JOIN mdl_course c ON c.id = ct.instanceid AND e.courseid = c.id

JOIN mdl_role r ON r.id = ra.roleid AND r.shortname = 'student'

WHERE e.status = 0 AND u.suspended = 0 AND u.deleted = 0

AND (ue.timeend = 0 OR ue.timeend > NOW()) AND ue.status = 0

我可以通过在查询结尾添加AND courseid = 11来返回特定课程的结果,但我仍在努力处理日期范围.

我尝试过使用：

AND ue.timecreated BETWEEN NOW() - INTERVAL 90 DAY AND NOW()

但是这回来没有结果我甚至试图添加2000天范围,它什么也没有返回

这是我当前修改过的查询：

SELECT DISTINCT u.id AS userid, c.id AS courseid, DATE_FORMAT(ue.timecreated, '%m/%d/%Y')

FROM mdl_user u

JOIN mdl_user_enrolments ue ON ue.userid = u.id

JOIN mdl_enrol e ON e.id = ue.enrolid

JOIN mdl_role_assignments ra ON ra.userid = u.id

JOIN mdl_context ct ON ct.id = ra.contextid

AND ct.contextlevel =50

JOIN mdl_course c ON c.id = ct.instanceid

AND e.courseid = c.id

JOIN mdl_role r ON r.id = ra.roleid

AND r.shortname = 'student'

WHERE e.status =0

AND u.suspended =0

AND u.deleted =0

AND (

ue.timeend =0

OR ue.timeend > NOW()

)

AND ue.status =0

AND ue.timecreated BETWEEN NOW() - INTERVAL 1200 DAY AND NOW()

AND courseid = 11

并且没有返回任何内容,只要我删除包含日期范围的倒数第二行,我就会收到课程内容11中的所有结果.

基本上我需要：

在过去90天内未注册或删除的特定课程中注册的所有用户的列表.

结果将是这样的：

课程编号：20

用户ID：200,533,221,22,356

干杯,

担