基于sql server中先前的差异值的两行之间的差异
我有一张像下面这样的桌子
ID Value
1 5
2 6
3 4
4 3
5 7
6 6
7 8
8 9输出类行差
ID Value difference
1 5 null
2 6 1
3 4 -2
4 3 -3
5 7 1
6 6 -1
7 8 1
8 9 -1上述产出的设想：
id values difference
1 5 1
2 6在上述情况下，差异>0，因此我们需要考虑id 2,3值才能找到差异。
id values difference
2 6 -2
3 4在上述情况下差异<=0，因此我们需要考虑id 2,4值来寻找差异。
id values difference
2 6 -3
4 3在上述情况下，差异<=0，因此我们需要考虑id 2,5值才能找到差异
id values difference
2 6 1
5 7在上述情况下，差异>0，因此需要考虑id 5、6值才能找到差异。
id values difference
5 7 -1
6 6 在上述情况下，差异<=0，因此我们需要考虑id 5,7值才能找到差异
id values difference
5 7 1
7 8在上述情况下，差异>0，因此需要考虑id 7、8值才能找到差异。
id values difference
7 8 -1
8 9 回答 2
Database Administration用户
发布于 2015-12-22 15:41:40
不管OP如何表述这个问题，以及问题背后缺乏背景，我认为一个可能的解决方案是使用一个时间周期。首先我得到了一个考虑：
OP提到，对于最后一行，结果应该是-1，如果您遵循原始模式，这是没有意义的。在我的脑海里是这样的：
5 = (5-X) Null
|_6 = (6-5) 1
|_4 = (4-6) -2 (Result is negative so retain 6)
|_3 = (3-6) -3
|_7 = (7-6) 1 (Drop the 6 since result became positive)
|_6 = (6-7) -1
|_8 = (8-7) 1
|_9 = (9-8) 1 (Minus 1?, I don't think so)因此，我进行了一个WHILE循环，循环遍历行，将相关关系加2，并将结果存储在变量中，然后，根据结果变量(正、负)的结果，它要么使用最后一个数字(正)进行，要么为下一次操作保留当前的结果(负值)。
CREATE TABLE #TempTest (id INT IDENTITY(1,1) PRIMARY KEY, num INT);
INSERT INTO #TempTest (num) VALUES (5), (6), (4), (3), (7), (6), (8), (9);
DECLARE
@CurrentNumber INT,
@PastNumber INT = NULL,
@i INT = 1,
@CurrentTotal INT
WHILE (@i<=(SELECT COUNT(*) FROM #TempTest))
BEGIN
SET @CurrentNumber = (SELECT num FROM #TempTest WHERE id = @i)
IF @PastNumber IS NOT NULL
BEGIN
SET @CurrentTotal = (@CurrentNumber - @PastNumber)
IF @CurrentTotal < 0
BEGIN
SET @PastNumber = @PastNumber
END
ELSE
BEGIN
SET @PastNumber = @CurrentNumber
END
END
ELSE
BEGIN
SET @PastNumber = @CurrentNumber
END
IF @CurrentTotal IS NULL
BEGIN
PRINT 'Null'
END
ELSE
BEGIN
PRINT @CurrentTotal
END
SET @i = @i+1
END这将输出：Null, 1, -2, -3, 1, -1, 1, 1，并且与问题中描述的不同，最后的数字将是1而不是-1。希望OP能提供更多的说明。
Database Administration用户
发布于 2015-12-23 16:48:54
以下是我对这个问题的看法：
WITH NextGreater AS
(
SELECT
t.ID,
NextID = x.ID
FROM
dbo.atable AS t
CROSS APPLY
(
SELECT TOP (1)
ID
FROM
dbo.atable
WHERE
Value > t.Value
AND ID > t.ID
ORDER BY
ID ASC
) AS x
),
Ranges AS
(
SELECT TOP (1)
StartID = 0,
EndID = ID
FROM
dbo.atable
ORDER BY
ID ASC
UNION ALL
SELECT
StartID = t.ID,
EndID = t.NextID
FROM
Ranges AS r
INNER JOIN NextGreater AS t ON r.EndID = t.ID
)
SELECT
td.ID,
td.Value,
difference = td.Value - t0.Value
FROM
ranges AS r
INNER JOIN dbo.atable AS td ON td.ID > r.StartID AND td.ID <= r.EndID
LEFT JOIN dbo.atable AS t0 ON t0.ID = r.StartID
ORDER BY
ID ASC
;正如您所看到的，它是作为一个语句实现的，这通常意味着它使用了一种基于集合的方法(这很好，因为对关系数据库系统进行了优化)。但是，这个查询不能真正限定为严格基于集的查询，因为它使用递归公共表表达式(CTE) --本质上是逐行设备，尽管它被称为“表达式”。
无论如何，下面是对该方法工作方式的描述：
- 第一个CTE，
NextGreater，查找表中每一行的第一行的ID，该ID位于当前行之后，并且Value大于当前行。它基本上创建了一组(初步的) ID范围。对于您的示例，它会产生以下结果: ID NextID 1 2 2 5 3 5 4 5 5 7 7 8 Ranges递归CTE只从前面的CTE结果集中提取出相邻的范围，从ID最低的行开始。它还添加了一个“零范围”，以0开头，以最低ID结束。- 主查询接受
Ranges的输出，并两次将原始数据集连接到它:第一次获取每个范围中in的行(更具体地说，使用大于StartID且小于或等于EndID的in)，第二次只获取StartID行。(第二个联接是外部联接，以防止筛选出0..1范围。)这样，每个StartID行都与位于同一范围内的所有其他行连接，因此您可以计算起始行的Value与其他每个行之间的差异。对于初始范围，差异自然会以NULL结束，因为0的StartID不存在，连接行集中的相应Value为null。因为范围相邻并覆盖整个表，所以对于所有行都会得到差异。
根据您的描述中没有涉及的一点，可以对此方法进行优化，以避免递归CTE (因此将提升到“基于纯集”)。我的意思是，如果值总是减少或增加到超过当前引用值(这是您的示例中的情况；您只是没有指定是否总是这样)，那么Ranges集可以在一步像这样中生成：
WITH Ranges AS
(
SELECT
StartID = 0,
EndID = (SELECT TOP (1) ID FROM dbo.atable ORDER BY ID ASC)
UNION ALL
SELECT
StartID = MIN(t.ID),
EndID = x.ID
FROM
dbo.atable AS t
CROSS APPLY
(
SELECT TOP (1)
ID
FROM
dbo.atable
WHERE
Value > t.Value
AND ID > t.ID
ORDER BY
ID ASC
) AS x
GROUP BY
x.ID
)
SELECT
... -- main query, same as before另一方面，由于没有定义值仅略微增加(不超过参考值)的逻辑，因此不清楚这两种变化是否会为您产生预期的输出。你可能需要在你的问题中详细说明这一点，这样你就可以有更多的选择。
https://dba.stackexchange.com/questions/124254
复制相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号