本文介绍了选择顶行,直到特定列中的值出现两次的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我有以下查询,我试图选择所有记录,按日期排序,直到第二次找到 EmailApproved = 1.不应选择 EmailApproved = 1 的第二条记录.

I have the following query where I am trying to select all records, ordered by date, until the second time EmailApproved = 1 is found. The second record where EmailApproved = 1 should not be selected.

declare @Test table (id int, EmailApproved bit, Created datetime);

insert into @Test (id, EmailApproved, Created)
values
  (1,0,'2011-03-07 03:58:58.423')
  , (2,0,'2011-02-21 04:55:52.103')
  , (3,0,'2011-01-29 13:24:02.103')
  , (4,1,'2010-10-12 14:41:54.217')
  , (5,0,'2010-10-12 14:34:15.903')
  , (6,0,'2010-10-12 10:10:19.123')
  , (7,1,'2010-08-27 12:07:16.073')
  , (8,1,'2010-08-25 12:15:49.413')
  , (9,0,'2010-08-25 12:14:51.970')
  , (10,1,'2010-04-12 16:43:44.777');

select *
  , case when Row1 = Row2 then 1 else 0 end Row1EqualRow2
from (
  select id, EmailApproved, Created
    , row_number() over (partition by EmailApproved order by Created desc) Row1
    , row_number() over (order by Created desc) Row2
  from @Test
) X
--where Row1 = Row2
order by Created desc;

产生以下结果:

id  EmailApproved   Created                 Row1    Row2    Row1EqualsRow2
1   0               2011-03-07 03:58:58.423 1       1       1
2   0               2011-02-21 04:55:52.103 2       2       1
3   0               2011-01-29 13:24:02.103 3       3       1
4   1               2010-10-12 14:41:54.217 1       4       0
5   0               2010-10-12 14:34:15.903 4       5       0
6   0               2010-10-12 10:10:19.123 5       6       0
7   1               2010-08-27 12:07:16.073 2       7       0
8   1               2010-08-25 12:15:49.413 3       8       0
9   0               2010-08-25 12:14:51.970 6       9       0
10  1               2010-04-12 16:43:44.777 4       10      0

我真正想要的是:

id  EmailApproved   Created                 Row1    Row2    Row1EqualsRow2
1   0               2011-03-07 03:58:58.423 1       1       1
2   0               2011-02-21 04:55:52.103 2       2       1
3   0               2011-01-29 13:24:02.103 3       3       1
4   1               2010-10-12 14:41:54.217 1       4       0
5   0               2010-10-12 14:34:15.903 4       5       0
6   0               2010-10-12 10:10:19.123 5       6       0

注意:Row, Row2 &Row1EqualsRow2 只是用来显示我的计算的工作列.

Note: Row, Row2 & Row1EqualsRow2 are just working columns to show my calculations.

推荐答案

步骤:

  1. 在所有行上创建一个行号 rn,以防 id 不按顺序排列.
  2. 创建一个行号,approv_rn,由 EmailApproved 分区,以便我们知道第二次 EmailApproved = 1 的时间
  3. 使用outer apply来查找EmailApproved = 1
  4. second实例的行号
  5. where 子句中,过滤掉所有行号为 >= 的行,即在步骤 3 中找到的值.
  6. 如果有 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;

这篇关于选择顶行,直到特定列中的值出现两次的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

05-27 08:12