mysql - 组合查询返回不同的结果

Question

我从两个不同的查询创建的查询结果没有返回相同的结果。

查询A

SELECT 
c.fullname Course,
u.firstname First,
u.lastname Last,
u.id ID,
u.institution Company

FROM (mdl_scorm_scoes_track AS st)
JOIN mdl_user AS u ON st.userid=u.id
JOIN mdl_scorm AS sc ON sc.id=st.scormid
JOIN mdl_course AS c ON c.id=sc.course
Join mdl_user_enrolments AS uenr ON uenr.userid=u.id
Join mdl_enrol AS enr ON enr.id=uenr.enrolid 

WHERE (
(st.value='incomplete' OR st.value='not attempted') 
AND DATEDIFF(NOW(), FROM_UNIXTIME(uenr.timecreated)>60)
ORDER BY c.fullname, u.lastname,u.firstname, u.id

查询B

SELECT
c.fullname AS Course,
u.firstname AS Firstname,
u.lastname AS Lastname,
u.id AS ID,
u.institution AS Company,

IF (u.lastaccess = 0,'never',
DATE_FORMAT(FROM_UNIXTIME(u.lastaccess),'%Y-%m-%d')) AS dLastAccess

,(SELECT DATE_FORMAT(FROM_UNIXTIME(timeaccess),'%Y-%m-%d') FROM mdl_user_lastaccess WHERE userid=u.id AND courseid=c.id) AS CourseLastAccess 
FROM mdl_user_enrolments AS ue
JOIN mdl_enrol AS e ON e.id = ue.enrolid
JOIN mdl_course AS c ON c.id = e.courseid
JOIN mdl_user AS u ON u.id = ue.userid
LEFT JOIN mdl_user_lastaccess AS ul ON ul.userid = u.id
WHERE ul.timeaccess IS NULL AND (DATEDIFF(NOW(), FROM_UNIXTIME(ue.timecreated))>60)
ORDER BY u.id, c.fullname

我已将它们合并到查询 C 中

SELECT
c.fullname AS Course,
u.firstname AS Firstname,
u.lastname AS Lastname,
u.id AS IDNumber,
u.institution AS Institution,

IF (u.lastaccess = 0,'never',
DATE_FORMAT(FROM_UNIXTIME(u.lastaccess),'%Y-%m-%d')) AS dLastAccess

,(SELECT DATE_FORMAT(FROM_UNIXTIME(timeaccess),'%Y-%m-%d') FROM mdl_user_lastaccess WHERE userid=u.id AND courseid=c.id) AS CourseLastAccess


FROM mdl_user_enrolments AS ue
JOIN mdl_enrol AS e ON e.id = ue.enrolid
JOIN mdl_course AS c ON c.id = e.courseid
JOIN mdl_user AS u ON u.id = ue.userid
LEFT JOIN mdl_user_lastaccess AS ul ON ul.userid = u.id
WHERE (ul.timeaccess IS NULL OR ue.userid IN
(SELECT u.id 
FROM (mdl_scorm_scoes_track AS st)
JOIN mdl_scorm AS sc ON sc.id=st.scormid

WHERE c.id=sc.course AND st.userid=u.id AND (st.value='incomplete' OR st.value='not attempted') 
 )
)AND (DATEDIFF(NOW(), FROM_UNIXTIME(ue.timecreated))>60)
ORDER BY c.fullname, u.lastname,u.firstname

我还没有发现查询 C 中的逻辑哪里不正确。查询 C 添加了 A 或 B 都没有找到的错误记录，并且在一些情况下重复了条目。

我想要一些关于我将两者结合起来的逻辑误入歧途的指示。

我修复了这篇文章中的逗号，实际查询确实有它们。

我的目的是列出所有已注册类(class)但在给定时间范围内尚未登录系统、访问类(class)且最终未完成类(class)中的事件的用户。

所以我正在寻找的逻辑是:如果用户在 60 天内未登录 - 显示名称如果已登录但 60 天内未访问类(class) - 显示名称如果登录并访问了类(class)但尚未完成类(class)事件 - 显示名称

查询 A 确实列出了已开始该事件但在 60 天内未完成的所有用户查询 B 确实列出了 60 天内未登录或访问类(class)的所有用户

在尝试组合 2 个查询来满足所有 3 个条件时，我遇到了问题。我首先尝试了 UNION 但无法让它工作。

Answer 1

您没有在两个查询中从相同的世界中进行选择。查询 A 有 6 个表可供选择，查询 B 有 5 个表(缺少 mdl_scorm 表)，查询 C 有查询 B 所拥有的 5 个表。现在您的查询 A 的范围已更改。该额外的表可能已经消除了连接中现在出现在查询 C 中用于 A 的选择的行。

我会检查查询 A，其中查询中缺少 mdl_scorm 表，并且您可能会得到不同的结果大小。

把它想象成

从过滤器的表中选择列；对于两个查询，表(数据域)需要相同，以便您只需加入列和过滤器(扩展选择和限制结果)