需要的SQL语句——结果来自一个范围，但不在另一个范围内

Question

我有两个表。

• 列出成员和
的所有详细信息的MEMBERS表
• 第二个表，存储每个列的所有更改的历史记录

在MEMBERS表中。例如，如果一个成员的状态在6月1日从Active变为Frozen，那么MEMBERCHANGES表中就会有一条表项表示这一变化。

我需要创建一个SQL语句，它列出了所有处于冻结状态的成员记录，这些记录在一个日期范围内更改为状态，但在第二个日期范围内没有任何更改。

例如，我需要所有在1月1日至5月1日期间状态更改为FREEZE，但在5月2日至8月31日期间状态字段没有任何更改的成员。

SELECT MEMBERS.scancode
    ,MEMBERS.fname
    ,MEMBERS.lname
    ,MEMBERTYPES.description
    ,MEMBERS.STATUS
    ,MEMBERS.email
    ,MEMBERS.datejoin
    ,MEMBERS.dateexpire
    ,MEMBERS.daterenewal
    ,MEMBERCHANGES.datechange
    ,MEMBERCHANGES.newvalue
FROM MEMBERS
INNER JOIN MEMBERCHANGES ON MEMBERS.memid = MEMBERCHANGES.memid
INNER JOIN MEMBERTYPES ON MEMBERS.mtypeid = MEMBERTYPES.mtypeid
    AND NOT EXISTS (
        SELECT MEMBERS.memid
        FROM MEMBERS AS MEM2
        INNER JOIN MEMBERCHANGES ON MEM2.memid = MEMBERCHANGES.memid
        INNER JOIN MEMBERTYPES ON MEM2.mtypeid = MEMBERTYPES.mtypeid
        WHERE (MEMBERCHANGES.columnname = 'status')
            AND (MEMBERCHANGES.newvalue = 'F')
            AND (
                MEMBERCHANGES.datechange BETWEEN '2015-05-02'
                    AND '2015-08-31'
                )
            AND (MEM2.STATUS = 'F')
        )
WHERE (MEMBERCHANGES.columnname = 'status')
    AND (
        MEMBERCHANGES.datechange BETWEEN '2015-01-01'
            AND '2015-05-01'
        )
    AND (MEMBERS.STATUS = 'F')
    AND (MEMBERCHANGES.newvalue = 'F')

Answer 1

我用别名清理了您的查询，并删除了一些我认为多余的东西。请原谅我把它改成小写。

select ...
from
    MEMBERS m
    inner join MEMBERCHANGES on mc.memid = m.memid
    inner join MEMBERTYPES on mt.mtypeid = m.mtypeid
where
        /* current status is Freeze */
        m.STATUS = 'F'
        /* changed to Freeze between certain dates */
    and mc.columnname = 'status'
    and mc.datechange between '2015-01-01' and '2015-05-01'
    and mc.newvalue = 'F'
        /* and no changes between later dates */
    and not exist (
        select 1
        from MEMBERCHANGES mc2
        where
                mc2.memid = mc.memid
            and mc2.columnname = 'status'
            and mc2.datechange between '2015-05-02' and '2015-08-31'
            and mc2.newvalue = 'F'
    )

由于当前日期已经过了8月31日，我想知道您核对当前状态是否有问题，因为在过去的一个半月里，当前状态可能发生了变化。我相信你了解你正在处理的数据。

当然也有可能在第一个窗口期间状态已经更改为冻结，但随后在同一窗口期间更改为其他状态。然后它现在的状态可能是冻结，因为在9月1日和现在之间发生了变化。我不知道你是否需要检查这类事情，但这个查询可能无法保证5月1日结束时的成员状态。