步骤:

1. 在所有行上创建一个行号 rn，以防 id 不按顺序排列.
2. 创建一个行号，approv_rn，由 EmailApproved 分区，以便我们知道第二次 EmailApproved = 1 的时间
3. 使用outer apply来查找EmailApproved = 1
的second实例的行号
4. 在 where 子句中，过滤掉所有行号为 >= 的行，即在步骤 3 中找到的值.
5. 如果有 1 或 0 EmailApproved 记录可用，则 outer apply 将返回 null，在这种情况下返回所有可用的行.

1. Create a row number, rn, over all rows in case id is not in sequence.
2. Create a row number, approv_rn, partitioned by EmailApproved so we know when EmailApproved = 1 for the second time
3. Use a outer apply to find the row number of the second instance of EmailApproved = 1
4. In the where clause filter out all rows where the row number is >= the value found in step 3.
5. If there is 1 or 0 EmailApproved records available then the outer apply will return null, in which case return all available rows.

with test as
(
    select  *, 
            rn         = row_number() over (order by Created desc),
            approv_rn  = row_number() over (partition by EmailApproved 
                                                order by Created desc)
    from    @Test
)
select  *
from    test t
        outer apply
        (
            select  x.rn
            from    test x
            where   x.EmailApproved = 1
            and     x.approv_rn     = 2
        ) x
where   t.rn    < x.rn or x.rn is null
order by t.Created desc;