Mysql SQL 根据多行值获取记录

我正在try 创建一个SQL报告，它可以基于存在于多行中的布尔值执行逻辑. 我在下面创造了一堆虚假的数据，我的try .

导入注意:我正在使用SSMS编写本文，但计划在MySQL中运行它.遇到一些临时表的问题，并且"无法重新打开表..."因此，我决定在熟悉的环境中工作，并在有突破的情况下致力于移植东西.

它在数据库中的存在方式是通过连接两个表，让我们称它们为Display和DisplaySchedule.

A)显示

CREATE 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', GETDATE()),
       (1241, 'Test Display 2', 1, 'Test Display 2', GETDATE()),
       (8311, 'Test Display 3', 1, 'Test Display 3', GETDATE())

B)显示时间表

CREATE 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', GETDATE() -1),
       (159123, 1241, 'Test Schedule 0', 0, 'OLD Schedule for Display 1 & 2', GETDATE() -1),
       (160213, 4210, 'Test Schedule 1', 0, 'Schedule for Display 1 & 2', GETDATE()),
       (160213, 4210, 'Test Schedule 1', 1, 'Schedule for Display 1 & 2', GETDATE()),
       (160213, 1241, 'Test Schedule 1', 1, 'Schedule for Display 1 & 2', GETDATE()),
       (160112, 8311, 'Test Schedule 2', 0, 'Schedule for Display 3', GETDATE()),
       (160112, 8311, 'Test Schedule 2', 1, 'Schedule for Display 3', GETDATE())

See the joined data:

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

我try 进行self 联接以获得预期的输出:

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，但它存在于另一个计划ID中并且处于活动状态 我不确定如何把这件事算出来.

我try 将DisSchedID添加到在SSMS中给出我结果的组中，但在MySQL中没有给出任何结果.

SELECT t1.DisId
FROM (
        SELECT dis.DisId, ds.DisSchedId
        FROM #tmp_display dis
        LEFT OUTER JOIN #tmp_displayschedule ds on dis.DisId = ds.DisId
        WHERE ds.DisSchedAct = 0
        GROUP BY dis.DisId, ds.DisSchedId
     ) as t1
JOIN
    (
        SELECT dis.DisId, ds.DisSchedId
        FROM #tmp_display dis
        LEFT OUTER JOIN #tmp_displayschedule ds on dis.DisId = ds.DisId
        WHERE ds.DisSchedAct = 1
        GROUP BY dis.DisId, ds.DisSchedId
     ) as t2 on t1.DisId = t2.DisId and t1.DisSchedId = t2.DisSchedId