mysql - 将 WHERE 子句中的 SUBQUERY 更改为 NORMAL JOIN

Question

我的新系统不接受嵌套/子查询。我需要帮助将以下 sql 转换为正常连接。我尝试了我的方法，但结果不一样:

SELECT
*
FROM
TRAINING.COURSE_SCHEDULE COURSE_SCHEDULE
LEFT JOIN
ENGINE.PHASE_SETTINGS PHASE_SETTINGS ON (COURSE_SCHEDULE.ETQ$CURRENT_PHASE = PHASE_SETTINGS.PHASE_ID)
LEFT JOIN
TRAINING.COURSE_PROFILE AS COURSE_PROFILE ON (COURSE_SCHEDULE.COURSE_PROFILE = COURSE_PROFILE.COURSE_PROFILE_ID)
LEFT JOIN
TRAINING.ETQ$COURSE_SCHEDULE_ASN ETQ$COURSE_SCHEDULE_ASN_1 ON (COURSE_SCHEDULE.COURSE_SCHEDULE_ID = ETQ$COURSE_SCHEDULE_ASN_1.COURSE_SCHEDULE_ID)
LEFT JOIN
ENGINE.USER_SETTINGS USER_SETTINGS_1 ON (ETQ$COURSE_SCHEDULE_ASN_1.ETQ$ASSIGNED = USER_SETTINGS_1.USER_ID)
LEFT JOIN
ENGINE.BOOLEAN_VALUES BOOLEAN_VALUES_1 ON (COURSE_SCHEDULE.TEST_IS_REQUIRED = BOOLEAN_VALUES_1.VALUE)
WHERE
PHASE_SETTINGS.PHASE_TYPE IN (5)
AND COURSE_PROFILE.COURSE_PROFILE_ID NOT IN


(SELECT COURSE_PROFILE.COURSE_PROFILE_ID
FROM TRAINING.COURSE_PROFILE COURSE_PROFILE
LEFT JOIN
TRAINING.ETQ$COURSE_PROFILE_TTL TEST_TEMPLATE_LINK ON (COURSE_PROFILE.COURSE_PROFILE_ID = TEST_TEMPLATE_LINK.COURSE_PROFILE_ID)
LEFT JOIN
TRAINING.ETQ$DOCUMENT_LINKS DOCUMENT_LINKS ON (TEST_TEMPLATE_LINK.TEST_TEMPLATE_ID = DOCUMENT_LINKS.LINK_ID)
LEFT JOIN
TRAINING.TEST_TEMPLATE ON (DOCUMENT_LINKS.DOCUMENT_ID = TEST_TEMPLATE.TEST_TEMPLATE_ID)
LEFT JOIN
TRAINING.TEST_DOCUMENT TEST_DOCUMENT ON (TEST_TEMPLATE.TEST_TEMPLATE_ID = TEST_DOCUMENT.TEST_TEMPLATE)
WHERE TEST_DOCUMENT_DATE IS NULL)

Answer 1

With 子句算作嵌套吗？

WITH UNWANTED AS(
    SELECT COURSE_PROFILE.COURSE_PROFILE_ID
    FROM TRAINING.COURSE_PROFILE COURSE_PROFILE
    LEFT JOIN TRAINING.ETQ$COURSE_PROFILE_TTL TEST_TEMPLATE_LINK ON (COURSE_PROFILE.COURSE_PROFILE_ID = TEST_TEMPLATE_LINK.COURSE_PROFILE_ID)
    LEFT JOIN TRAINING.ETQ$DOCUMENT_LINKS DOCUMENT_LINKS ON (TEST_TEMPLATE_LINK.TEST_TEMPLATE_ID = DOCUMENT_LINKS.LINK_ID)
    LEFT JOIN TRAINING.TEST_TEMPLATE ON (DOCUMENT_LINKS.DOCUMENT_ID = TEST_TEMPLATE.TEST_TEMPLATE_ID)
    LEFT JOIN TRAINING.TEST_DOCUMENT TEST_DOCUMENT ON (TEST_TEMPLATE.TEST_TEMPLATE_ID = TEST_DOCUMENT.TEST_TEMPLATE)
    WHERE TEST_DOCUMENT_DATE IS NULL
)
SELECT *
FROM TRAINING.COURSE_SCHEDULE COURSE_SCHEDULE
LEFT JOIN ENGINE.PHASE_SETTINGS PHASE_SETTINGS ON (COURSE_SCHEDULE.ETQ$CURRENT_PHASE = PHASE_SETTINGS.PHASE_ID)
LEFT JOIN TRAINING.COURSE_PROFILE AS COURSE_PROFILE ON (COURSE_SCHEDULE.COURSE_PROFILE = COURSE_PROFILE.COURSE_PROFILE_ID)
LEFT JOIN TRAINING.ETQ$COURSE_SCHEDULE_ASN ETQ$COURSE_SCHEDULE_ASN_1 ON (COURSE_SCHEDULE.COURSE_SCHEDULE_ID = ETQ$COURSE_SCHEDULE_ASN_1.COURSE_SCHEDULE_ID)
LEFT JOIN ENGINE.USER_SETTINGS USER_SETTINGS_1 ON (ETQ$COURSE_SCHEDULE_ASN_1.ETQ$ASSIGNED = USER_SETTINGS_1.USER_ID)
LEFT JOIN ENGINE.BOOLEAN_VALUES BOOLEAN_VALUES_1 ON (COURSE_SCHEDULE.TEST_IS_REQUIRED = BOOLEAN_VALUES_1.VALUE)
LEFT JOIN UNWANTED UNW on UNW.COURSE_PROFILE_ID = COURSE_PROFILE.COURSE_PROFILE_ID
WHERE PHASE_SETTINGS.PHASE_TYPE IN (5)
AND UNW.COURSE_PROFILE_ID IS NULL;

我知道 WITH 是嵌套子查询的解决方案，如果这对您痛苦的限制没有帮助，我很抱歉。

更新:

好吧，让我们尝试这种方式，基本上应用与上面相同的逻辑，但是子查询现在与您的主SELECT集成，我相信这就是您首先要寻找的:

SELECT *
FROM TRAINING.COURSE_SCHEDULE AS COURSE_SCHEDULE
LEFT JOIN ENGINE.PHASE_SETTINGS AS PHASE_SETTINGS ON (COURSE_SCHEDULE.ETQ$CURRENT_PHASE = PHASE_SETTINGS.PHASE_ID)
LEFT JOIN TRAINING.COURSE_PROFILE AS COURSE_PROFILE ON (COURSE_SCHEDULE.COURSE_PROFILE = COURSE_PROFILE.COURSE_PROFILE_ID)
LEFT JOIN TRAINING.ETQ$COURSE_SCHEDULE_ASN AS ETQ$COURSE_SCHEDULE_ASN_1 ON (COURSE_SCHEDULE.COURSE_SCHEDULE_ID = ETQ$COURSE_SCHEDULE_ASN_1.COURSE_SCHEDULE_ID)
LEFT JOIN ENGINE.USER_SETTINGS AS USER_SETTINGS_1 ON (ETQ$COURSE_SCHEDULE_ASN_1.ETQ$ASSIGNED = USER_SETTINGS_1.USER_ID)
LEFT JOIN ENGINE.BOOLEAN_VALUES AS BOOLEAN_VALUES_1 ON (COURSE_SCHEDULE.TEST_IS_REQUIRED = BOOLEAN_VALUES_1.VALUE)
LEFT JOIN TRAINING.COURSE_PROFILE AS COURSE_PROFILE_2 ON COURSE_PROFILE_2.COURSE_PROFILE_ID = COURSE_PROFILE.COURSE_PROFILE_ID
LEFT JOIN TRAINING.ETQ$COURSE_PROFILE_TTL AS TEST_TEMPLATE_LINK ON (COURSE_PROFILE_2.COURSE_PROFILE_ID = TEST_TEMPLATE_LINK.COURSE_PROFILE_ID)
LEFT JOIN TRAINING.ETQ$DOCUMENT_LINKS AS DOCUMENT_LINKS ON (TEST_TEMPLATE_LINK.TEST_TEMPLATE_ID = DOCUMENT_LINKS.LINK_ID)
LEFT JOIN TRAINING.TEST_TEMPLATE ON (DOCUMENT_LINKS.DOCUMENT_ID = TEST_TEMPLATE.TEST_TEMPLATE_ID)
LEFT JOIN TRAINING.TEST_DOCUMENT AS TEST_DOCUMENT ON (TEST_TEMPLATE.TEST_TEMPLATE_ID = TEST_DOCUMENT.TEST_TEMPLATE
                                                      AND TEST_DOCUMENT.TEST_DOCUMENT_DATE IS NULL)
WHERE PHASE_SETTINGS.PHASE_TYPE IN (5)
AND COURSE_PROFILE_2.COURSE_PROFILE_ID IS NULL;

请注意，我将 COURSE_PROFILE 的别名从原始嵌套查询更改为 COURSE_PROFILE_2，因此它不会与主查询中的 COURSE_PROFILE 冲突，并且由于原始子查询中的列 TEST_DOCUMENT_DATE 没有附加表别名，我从它的名称推断它属于 TEST_DOCUMENT 表，如果是，请更改它错误的。祝你好运。