表上每 N 条记录增加列上的值 [英] Increment value on column every N records on table

问题描述

我需要在表列上每 4 条记录增加 +1，我尝试使用 ROW_NUM() 但我的肮脏解决方法没有意义.

I need to increment +1 every 4 records over a table column, I've tried to use ROW_NUM() but my dirty workaround does not make sense.

这就是我需要的:

Index PeriodID
1       1
1       2
1       3
1       4
2       5
2       6
2       7
2       8

PeriodID 是表Periods"的主键(聚集索引)，我听说过窗口函数 LAG() 和 LEAD() 但不确定我是否可以将这个概念应用于这个场景，以下语法是我的失败的肮脏伎俩尝试:

PeriodID is the primary key (clustered index) for table "Periods", I've heard about window functions LAG() and LEAD() but not sure if I can apply the concept for this scenario, the following syntax is my failed dirty trick attempt:

选择row_number() over (order by periodid)/4+1,周期从时期

select row_number() over (order by periodid)/4+1, periodid from periods

我得到的结果:

Index PeriodID
1       1
1       2
1       3
2       4
2       5
2       6
2       7
3       8

我明白为什么我会得到这个结果，但我想知道是否有内置的 T-SQL 函数可以帮助实现我所需要的.

I understand why I'm getting this result but I would like to know if there is a built in T-SQL function that can help to achieve what I need.

欢迎任何想法和建议

谢谢

推荐答案

我不太熟悉解决这类问题的内置函数，但你可以用简单的数学来解决(我相信有人可以收紧这只是第一次通过):

I am not that familiar with built-ins to solve this kind of problem, but you can do it with plain math (and I'm sure someone could tighten this up, just a first pass):

CREATE TABLE #t (PeriodID int PRIMARY KEY, ExpectedIndex int);
INSERT INTO #t VALUES (1, 1),(2, 1),(3, 1),(4, 1),(5, 2),(6, 2),(7, 2),(8, 2),(9, 3),(10, 3),(11, 3),(13, 3);

SELECT ((RowNum - ((RowNum - 1) % 4)) / 4) + 1 AS [Index], ExpectedIndex, PeriodID
FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY PeriodID) AS RowNum, ExpectedIndex, PeriodID FROM #t
    ) AS tSub1

DROP TABLE #t;

编辑:我很好奇，这是一个滞后版本(同样，毫无疑问没有优化)，加上子查询出来只是为了显示我的作品:

EDIT: I was curious, here's a LAG version (again, no doubt not optimized), plus subqueried out just to Show My Work:

SELECT
    (RowNum - IncrementalLag) / 4 AS ZeroBasedIndex
    ,((RowNum - IncrementalLag) / 4) + 1 AS [Index]
    ,ExpectedIndex
    ,PeriodID
FROM
    (
    SELECT 
        RowNum
        ,LAG(RowNum,1,0) OVER (ORDER BY RowNum) % 4 AS IncrementalLag
        ,ExpectedIndex
        ,PeriodID
    FROM (
        SELECT ROW_NUMBER() OVER (ORDER BY PeriodID) AS RowNum, ExpectedIndex, PeriodID FROM #t
        ) AS tSub1
    ) AS tSub2;

这篇关于表上每 N 条记录增加列上的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！