如何获取其他表中不存在的数据？SQL查询需求补充

要实现你需要的效果，原查询的内连接逻辑得调整——内连接只会返回两张表都有匹配的记录，没法拿到maintenance_schedule里没有对应数据的activity_detail行。改成左连接并调整过滤条件就能解决问题，具体修改后的SQL如下：

SELECT
    instrument_no,
    frequency,
    detail,
    plan_id,
    activity_id,
    period,
    maintenance_due_date
FROM
(
    SELECT
        ms.instrument_no,
        ad.frequency,
        ad.detail,
        ad.plan_id,
        ad.activity_id,
        ad.period,
        MAX(ms.maintenance_due_date) maintenance_due_date
    FROM
        activity_detail ad
    LEFT JOIN maintenance_schedule ms 
        ON ad.plan_id = ms.plan_id 
        AND ad.activity_id = ms.activity_id 
        AND ms.instrument_no = 10073
    WHERE
        ad.plan_id = 100
        AND ad.end_date IS NULL
    GROUP BY
        ms.instrument_no,
        ad.frequency,
        ad.detail,
        ad.plan_id,
        ad.activity_id,
        ad.period
)
WHERE
    -- 保留到期日期早于当前、未来7天内，或者无对应维护计划的记录
    (maintenance_due_date < SYSDATE 
     OR maintenance_due_date BETWEEN SYSDATE AND SYSDATE + 7
     OR maintenance_due_date IS NULL)
ORDER BY activity_id ASC, maintenance_due_date ASC;

关键修改点说明 #

• 左连接替换内连接：以activity_detail为主表左连maintenance_schedule，确保activity_detail中符合条件的记录全部被保留，哪怕没有对应的维护计划数据
• 调整关联条件：把instrument_no的匹配条件放到连接子句里，避免左连接被WHERE条件转成内连接
• 新增空值过滤：在WHERE子句里加入OR maintenance_due_date IS NULL，让无对应维护计划的记录也能被筛选出来
• 分组逻辑保留：依然用MAX(ms.maintenance_due_date)获取每个活动对应的最新到期日期，无数据时会返回NULL

内容的提问来源于stack exchange，提问作者Moin Khan