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

这个问题的棘手版本：SQL-添加从条件开始的顺序计数器列 https://stackoverflow.com/questions/57897292/sql-add-sequential-counter-column-starting-at-condition

我有一张桌子：

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

首先它生成cnt by category

然后它找到cnt for special按类别

决赛count只是减去cnt with cnt of special

;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

Note : left join special被使用，因为有一些category没有special