基于多行布尔值筛选DisId的SQL查询兼容问题（SSMS转MySQL）

测试表结构与数据（MySQL兼容版本） #

a) display临时表 #

CREATE TEMPORARY TABLE tmp_display (
    DisId int,
    DisName varchar(255),
    DisActive tinyint,
    Descr varchar(255),
    DateModified datetime
);

INSERT INTO tmp_display(DisId, DisName, DisActive, Descr, DateModified)
VALUES (4210, 'Test Display 1', 1, 'Test Display 1', NOW()),
       (1241, 'Test Display 2', 1, 'Test Display 2', NOW()),
       (8311, 'Test Display 3', 1, 'Test Display 3', NOW());

b) displayschedule临时表 #

CREATE TEMPORARY TABLE tmp_displayschedule (
    DisSchedId int,
    DisId int,
    DisSchedName varchar(255),
    DisSchedAct tinyint,
    Descr varchar(255),
    DateModified datetime
);

INSERT INTO tmp_displayschedule(DisSchedId, DisId, DisSchedName, DisSchedAct, Descr, DateModified)
VALUES (159123, 4210, 'Test Schedule 0', 0, 'OLD Schedule for Display 1 & 2', DATE_SUB(NOW(), INTERVAL 1 DAY)),
       (159123, 1241, 'Test Schedule 0', 0, 'OLD Schedule for Display 1 & 2', DATE_SUB(NOW(), INTERVAL 1 DAY)),
       (160213, 4210, 'Test Schedule 1', 0, 'Schedule for Display 1 & 2', NOW()),
       (160213, 4210, 'Test Schedule 1', 1, 'Schedule for Display 1 & 2', NOW()),
       (160213, 1241, 'Test Schedule 1', 1, 'Schedule for Display 1 & 2', NOW()),
       (160112, 8311, 'Test Schedule 2', 0, 'Schedule for Display 3', NOW()),
       (160112, 8311, 'Test Schedule 2', 1, 'Schedule for Display 3', NOW());

基础关联查询 #

SELECT * 
FROM tmp_display dis
LEFT OUTER JOIN tmp_displayschedule ds ON dis.DisId = ds.DisId
ORDER BY ds.DateModified ASC;

尝试过程与问题 #

首次自连接查询 #

SELECT t1.DisId
FROM (
        SELECT dis.DisId
        FROM tmp_display dis
        LEFT OUTER JOIN tmp_displayschedule ds ON dis.DisId = ds.DisId
        WHERE ds.DisSchedAct = 0
        GROUP BY dis.DisId
     ) as t1
JOIN
    (
        SELECT dis.DisId
        FROM tmp_display dis
        LEFT OUTER JOIN tmp_displayschedule ds ON dis.DisId = ds.DisId
        WHERE ds.DisSchedAct = 1
        GROUP BY dis.DisId
     ) as t2 ON t1.DisId = t2.DisId;

当前输出

DisId
1241
4210
8311

预期输出

DisId
4210
8311

问题分析 #

DisId 1241被错误筛选，核心原因是：它仅在旧调度ID（159123）有非激活记录，在新调度ID（160213）只有激活记录，并非同一个调度ID下同时存在激活和非激活记录。而首次查询仅判断DisId存在任意激活/非激活记录，未关联调度ID维度。

加入DisSchedId分组的查询在SSMS可得到正确结果，但MySQL中无输出，是因为MySQL默认开启ONLY_FULL_GROUP_BY模式，要求SELECT子句中的列必须包含在GROUP BY中或使用聚合函数，原查询的子SELECT逻辑不符合该规则，同时临时表的处理逻辑也存在差异。

MySQL适配解决方案 #

方案1：分组聚合判断（推荐） #

按DisId和DisSchedId分组，通过聚合函数检查每个分组内同时存在DisSchedAct=0和DisSchedAct=1的记录，最后去重得到目标DisId：

SELECT DISTINCT ds.DisId
FROM tmp_displayschedule ds
GROUP BY ds.DisId, ds.DisSchedId
HAVING MIN(ds.DisSchedAct) = 0 AND MAX(ds.DisSchedAct) = 1;

方案2：关联子查询验证 #

通过子查询确认每个DisId在同一个调度ID下同时存在激活和非激活状态：

SELECT DISTINCT dis.DisId
FROM tmp_display dis
WHERE EXISTS (
    SELECT 1
    FROM tmp_displayschedule ds1
    JOIN tmp_displayschedule ds2 ON ds1.DisId = ds2.DisId AND ds1.DisSchedId = ds2.DisSchedId
    WHERE ds1.DisId = dis.DisId
      AND ds1.DisSchedAct = 0
      AND ds2.DisSchedAct = 1
);

结果验证 #

上述两种方案均可得到预期输出：

DisId
4210
8311

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