本文介绍了SQL - 添加从条件开始的顺序计数器列,具有分组依据和非递增 ID的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
这个问题的棘手版本:SQL - 从条件开始添加顺序计数器列
我有一张桌子:
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
列不一定是增量的?
How can I select all columns from the table while also adding a sequential counter column, which starts counting once a condition has been triggered (in this case market='special'
), grouped by category
and with an id
column that is not necessarily incremental?
结果应该是这样的:
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
推荐答案
首先通过category
然后它按类别找到special
的cnt
最后的count
就是用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
Note : left join special
is used as there are some category
without special
这篇关于SQL - 添加从条件开始的顺序计数器列,具有分组依据和非递增 ID的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!