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

这个问题的棘手版本:
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

最佳答案

首先它通过 cnt 生成 category
然后它按类别找到 cnt 的 special
最后的 count 只是用 cnt 的 cnt 减去 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

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

关于tsql - SQL - 添加从条件开始的顺序计数器列，具有分组依据和非递增 ID，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/57905533/

Special

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