tsql - SQL - 添加从条件开始的顺序计数器列，具有分组依据和非递增 ID

Question

这个问题的棘手版本: SQL - add sequential counter column starting at condition

我有一张 table :

create table t (id int, category varchar(4), time1 datetime, market varchar(100));
insert into t (id,category,time1, market) values 
  (1000, 'cat1', '20120618 10:14:09 AM',   'mkt1'), 
  (1002, 'cat1', '20120618 10:14:11 AM',   'mkt2'), 
  (1001, 'cat2', '20120618 10:14:14 AM',  'mkt1'), 
  (1004, 'cat2', '20120618 10:14:15 AM',   'special'), 
  (1003, 'cat2', '20120618 10:14:17 AM',   'mkt2'), 
  (1006, 'cat1', '20120618 10:14:54 AM',   'special'), 
  (1007, 'cat3',  '20120618 10:14:55 AM',  'special'), 
  (1054, 'cat1', '20120618 10:14:58 AM',   'mkt1'), 
  (1023, 'cat1', '20120618 10:14:59 AM',   'mkt2'), 
  (1008, 'cat4', '20120618 10:24:09 AM',  'mkt1'), 
  (1028, 'cat2', '20120618 10:24:10 AM',   'special'), 
  (1021, 'cat1', '20120618 10:24:29 AM',   'mkt2'), 
  (1017, 'cat1', '20120618 10:34:49 AM',   'mkt2'), 
  (1019, 'cat1',  '20120618 10:34:57 AM',  'special')

我如何从表中选择所有列，同时添加一个顺序计数器列，该列在触发条件后开始计数(在本例中为 market='special')，按 category 并且 id 列不一定是增量的？

结果应该是这样的:

id    category  time1                  market  count
1000  cat1      20120618 10:14:09 AM   mkt1    0
1002  cat1      20120618 10:14:11 AM   mkt2    0
1001  cat2      20120618 10:14:14 AM   mkt1    0
1004  cat2      20120618 10:14:15 AM   special 1
1003  cat2      20120618 10:14:17 AM   mkt2    2
1006  cat1      20120618 10:14:54 AM   special 1
1007  cat3      20120618 10:14:55 AM   special 1
1054  cat1      20120618 10:14:58 AM   mkt1    2
1023  cat1      20120618 10:14:59 AM   mkt2    3
1008  cat4      20120618 10:24:09 AM   mkt1    0
1028  cat2      20120618 10:24:10 AM   special 3
1021  cat1      20120618 10:24:29 AM   mkt2    4
1017  cat1      20120618 10:34:49 AM   mkt2    5
1019  cat1      20120618 10:34:57 AM   special 6

Answer 1

首先它根据category生成cnt

然后它按类别找到special的cnt

最后的count只是用special的cnt减去cnt

;with 
cte as 
(
    select  t.id, t.category, t.market, t.time1, 
            cnt = row_number() over (partition by t.category order by t.time1)
    from    t 
),
special as
(
    select  category, cnt = min(cnt)
    from    cte
    where   market  = 'special'
    group by category
)
select  c.id, c.category, c.market, c.time1, 
        [count]     = case  when    c.cnt < s.cnt 
                            or      s.cnt is null 
                            then    0 
                            else    c.cnt - s.cnt + 1 
                            end
from    cte c
        left join special s on  c.category  = s.category
order by time1

注意:使用left join special是因为有些category没有special