使用当前行值更新变量 [英] Updating Variable with Current Row Value

问题描述

我正在尝试执行一个复杂的操作，我提取整列数据的总和，然后从每行的总和中减去运行小计.我可以单独完成 Sum 和 Running Subtotal 的组成部分.用于运行小计:

I am trying to perform a complex operation where I pull the sum for an entire column of data and subtract the running subtotal from the sum for each row. I can do the component parts of Sum and Running Subtotal alone. Used this for running subtotal:

sum(UsageMetric) over(order by Nested1.IDNumber) as RunningTotal

但是，我在尝试获取它们时遇到此错误:

However, I get this error when trying to comine them:

子查询返回了 1 个以上的值.当子查询跟在 =, !=, <, <= , >, >= 或使用子查询时，这是不允许的作为表达式.

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

所以我进行了调整，它返回给定行的独立"运行小计:

So I rigged this up, and it returns the 'stand-alone' running subtotal for a given row:

declare @TargetNumber BIGINT
Set @TargetNumber=(select Nested1.IDNumber from TargetTable Nested1 where Nested1.IdNumber='1234567890' and (Extraneous Criteria Here))

select  sum(Usage.UsageMetric) 
from Table_Usage Usage, IDTable IDT
where IDT.IDNumber <= @TargetNumber
and (Extraneous Criteria Here)

但我真正想做的是能够删除限定符Nested1.IDNumber='1234567890'，然后对 TargetTable 中的每个 IDNumber 执行此操作.

But what I would really like to do is be able to remove the qualifier "Nested1.IDNumber='1234567890' and just perform this for each IDNumber in TargetTable.

推荐答案

如果我理解正确，您可以执行以下操作:

If I understand you correctly, you could do the following:

sum(UsageMetric) over () -
sum(UsageMetric) over (order by Nested1.IDNumber) as ...

虽然简单地恢复 OVER 子句中的行顺序会产生相同的结果，但我相信:

Although simply reverting the order of rows in the OVER clause would yield the same results, I believe:

sum(UsageMetric) over (order by Nested1.IDNumber DESC) as ...

这篇关于使用当前行值更新变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！