Trying to find an efficient way to create the column C values which is based on column B and increments based on B being 1 or -1 and the count restarts when B switches between B being 1 or -1.
试图找到一种有效的方法来创建基于列B的列C值和基于B为1或-1的增量，并且当B在B之间切换为1或-1时计数重新开始。
CREATE TABLE #T (a int, b int, c int);
INSERT INTO #T VALUES (1, 1, 1),
(2, 1, 2),
(3, -1, -1),
(4, -1, -2),
(5, -1, -3),
(6, -1, -4),
(7, 1, 1),
(8, 1, 2);
Thank you!
谢谢！
1 个解决方案
#1
4
The trick is to group column b. You can do this with the base code taken from this answer. Now you need to switch the incrementer to be positive or negative. Easiest way to do that is to multiply the value by column b. For example:
诀窍是对列b进行分组。您可以使用从此答案中获取的基本代码执行此操作。现在您需要将增量器切换为正或负。最简单的方法是将值乘以b列。例如：
SELECT
a,b,
ROW_NUMBER() OVER (PARTITION BY b, grp ORDER BY a) * b as c
FROM (SELECT T.*,
(ROW_NUMBER() OVER (ORDER BY a) -
ROW_NUMBER() OVER (PARTITION BY b ORDER BY a)
) as grp
FROM T) T
ORDER BY a
#1
4
The trick is to group column b. You can do this with the base code taken from this answer. Now you need to switch the incrementer to be positive or negative. Easiest way to do that is to multiply the value by column b. For example:
诀窍是对列b进行分组。您可以使用从此答案中获取的基本代码执行此操作。现在您需要将增量器切换为正或负。最简单的方法是将值乘以b列。例如：
SELECT
a,b,
ROW_NUMBER() OVER (PARTITION BY b, grp ORDER BY a) * b as c
FROM (SELECT T.*,
(ROW_NUMBER() OVER (ORDER BY a) -
ROW_NUMBER() OVER (PARTITION BY b ORDER BY a)
) as grp
FROM T) T
ORDER BY a