时间戳

首页 > 解决方案 > sql server 计算表中的递归

sql-server - sql server 计算表中的递归

问题描述

我现在拥有的是一个结构像这样的表

. Current table (desired) Start END STATUS SELECTION | Cycle 2018-11-03 11:45:00.000 2018-11-03 11:45:00.000 0 0 | 0 2018-11-04 06:45:00.000 2018-11-04 07:26:20.000 2 0 | 1 2018-11-04 07:26:20.000 2018-11-04 08:16:35.000 5 0 | 1 2018-11-04 08:16:35.000 2018-11-04 08:16:55.000 6 0 | 1 2018-11-04 08:16:55.000 2018-11-04 08:19:00.000 2 0 | 1 2018-11-04 08:19:00.000 2018-11-04 08:20:10.000 3 0 | 1 2018-11-04 08:20:10.000 2018-11-04 08:25:30.000 0 0 | 1 2018-11-04 08:25:30.000 2018-11-04 08:25:55.000 1 0 | 2 2018-11-04 08:25:55.000 2018-11-04 08:54:25.000 2 0 | 2 2018-11-04 08:54:25.000 2018-11-04 09:02:25.000 5 0 | 2 2018-11-04 09:02:25.000 2018-11-04 09:03:00.000 3 0 | 2 2018-11-04 09:03:00.000 2018-11-04 09:39:44.000 0 0 | 2 2018-11-04 09:39:44.000 2018-11-04 23:17:14.000 0 0 | 2 2018-11-04 23:17:14.000 2018-11-05 00:55:35.000 0 0 | 2 2018-11-05 00:55:35.000 2018-11-05 00:56:00.000 1 0 | 3 2018-11-05 00:56:00.000 2018-11-05 03:20:36.000 2 0 | 3 2018-11-05 03:20:36.000 2018-11-05 03:52:05.000 2 0 | 3

我需要一个我称之为 Cycle 的新列，它能够“计算”状态从 0 变为不同于 0 的任何值（描述本描述的机器进程的开始）并保持不同于 0 的次数（当它返回 0 表示进程已完成一个周期）。我尝试使用与变量绑定的计数器来分配一个值，该值会根据状态值逐行更改，以将值分配给循环列。

Visual Studio 不允许我尝试说已为列分配了固定值，因此它无法分配该值。

有人建议使用排名函数，但我从 MS 文档中不清楚它是如何工作的，因此我不清楚如何应用于该问题

标签： sql-server

解决方案

必须有一个更漂亮的方法来做到这一点，但目前我很难过。我用您的示例数据创建了一个表变量，您所说的这个附加列包含一个日期，可用于订购。由于我缺乏想象力，所以我使用了一个int，但逻辑应该有效。

declare @t table (Status int not null, Cycle int not null, ReallyDate int not null)
insert into @t(Status,Cycle,ReallyDate) values
(0,0,1 ),    (2,1,2 ),    (5,1,3 ),    (6,1,4 ),    (2,1,5 ),    (3,1,6 ),
(0,1,7 ),    (1,2,8 ),    (2,2,9 ),    (5,2,10),    (3,2,11),    (0,2,12),
(0,2,13),    (0,2,14),    (1,3,15),    (2,3,16),    (2,3,17),    (5,3,18),
(6,3,19),    (2,3,20),    (5,3,21),    (3,3,22),    (0,3,23);

With Numbered as (
    select
        *,
        ROW_NUMBER() OVER (ORDER BY ReallyDate) as rn
    from @t
), Roots as (
    select
        t1.*,
        ROW_NUMBER() OVER (ORDER BY t1.ReallyDate) as CyclePrime
    from
        Numbered t1
            left join
        Numbered t2
            on
                t1.rn = t2.rn - 1 and
                t2.Status = 0
    where
        t1.Status = 0 and
        t2.rn is null
)
select
    n.*,
    COALESCE(r.CyclePrime,0) as Cycle
from
    Numbered n
        left join
    Roots r
        left join
    Roots r_anti
        on
            r_anti.CyclePrime = r.CyclePrime + 1
        on
            n.rn > r.rn and
            n.rn <= r_anti.rn

当然，我的数据已经有一个我可以使用而不是NumberedCTE 的列，但是你有一个 datetime 列并且需要生成类似rn.

然后Roots是棘手的一个。它标识0没有紧跟另一行的0行。这些我们用来分配循环编号。

在最后一个选择中，我们尝试将我们正在使用的任何行加入到前Root一行，并从该行中获取循环#。

结果（包括奖金栏）：

Status      Cycle       ReallyDate  rn                   Cycle
----------- ----------- ----------- -------------------- --------------------
0           0           1           1                    0
2           1           2           2                    1
5           1           3           3                    1
6           1           4           4                    1
2           1           5           5                    1
3           1           6           6                    1
0           1           7           7                    1
1           2           8           8                    2
2           2           9           9                    2
5           2           10          10                   2
3           2           11          11                   2
0           2           12          12                   2
0           2           13          13                   2
0           2           14          14                   2
1           3           15          15                   3
2           3           16          16                   3
2           3           17          17                   3
5           3           18          18                   3
6           3           19          19                   3
2           3           20          20                   3
5           3           21          21                   3
3           3           22          22                   3
0           3           23          23                   3

推荐阅读