SQL Server查询，在视图中运行总计，在A列更改时重置 [英] SQL Server Query, running total in view, reset when column A changes

问题描述

我的一个同事遇到了一个问题，我正在尝试帮助他.

A colleague of mine has a problem which I am trying to help him with.

他有一个SQL视图，其中包含以下数据(示例数据):-

He has a SQL view which contains the following data (Sample data):-

Category          Value
Cat A             10
Cat A             20
Cat A             30
Cat B             15
Cat B             15
Cat C             10
Cat C             10

他想在视图中添加一列，以保持Value列的运行总计.

He wants to add a column to the view which keeps a running total of the Value column.

当类别更改时，此列必须重置运行总计.

This column must reset the running total when the Category changes.

所以输出数据必须是这样的:-

So the output data must be like this:-

Category          Value       Running
Cat A             10          10
Cat A             20          30
Cat A             30          60
Cat B             15          15
Cat B             15          30
Cat C             10          10
Cat C             10          20

我们可以通过将表连接到自身上来获得运行总计:-

We could get the running total by joining the table onto itself like this:-

select t1.id, t1.[count], SUM(t2.[count]) as sum
from TableA t1
inner join TableA t2 on t1.id >= t2.id
group by t1.id, t1.[count]
order by t1.id

问题是我们没有ID列，当类别更改时我们如何指示运行总计重置??

Problem is we don't have an ID column and how would we instruct the running total to reset when the category changes ??

推荐答案

这在较大的表上效果不佳，但是可以完成工作！

This will not perform particularly well on larger tables, but will do the job!

   select 'Cat A' as class,10 as value into #x
UNION ALL SELECT 'Cat A',20 
UNION ALL SELECT 'Cat A',30 
UNION ALL SELECT 'Cat B',15 
UNION ALL SELECT 'Cat B',15 
UNION ALL SELECT 'Cat C',10 
UNION ALL SELECT 'Cat C',10 

;WITH running_total AS
(
select *
,ROW_number() OVER (PARTITION BY class order by value ASC) as row 
from #x 
)
SELECT 
r1.class
,MAX(r1.value) as value
,SUM(r2.value) as running_total
FROM running_total r1
LEFT OUTER JOIN running_total r2 on r2.class = r1.class
                AND r2.row <= r1.row
GROUP BY 
r1.class
,r1.row

这通过使用公用表表达式(CTE)和窗口函数来工作.CTE的工作方式类似于子查询，在子查询中，第一部分(称为running_total)会根据值向每个类添加基于行的ID.

This works by using a Common table Expression (CTE) and windowed functions. The CTE works in a similar fashion to a subquery, where the first part (called running_total), adds a row-based ID to each class based on value.

Row_number()按此处，但基本上，它会根据您定义分区的方式(基本上与group by相同，但不必在主查询中进行分组)和按类的内置顺序自动增加.

Row_number() works as documented here but basically it auto-increments depending on how you define partitioning (essentially the same as group by, but without having to group in the main query) and an in-built order by class.

在此示例中，按类别划分的分区确保每个新类别名称的最小值都设置为1-如果您希望不基于特定类别的总运行总计，则应删除该子句的这一部分.

In this example partitionign by class ensures that each new category name will have the lowest value set to 1 - if you wanted a total running total not based on specific categories you would remove this part of the clause.

CTE的第二部分从CTE的第一部分中选择结果，并在类匹配的地方加入自身.通过在r2.row< = r1.row上联接，可以确保第二个联接包括所有值< =当前行-即R1中的第3行将在R2联接中包括行1,2,3.

The second part of the CTE selects the results from the first part of the CTE and joins onto itself where classes match. By joining on r2.row <= r1.row this ensures that the second join includes all values <= the current row - i.e. Row 3 in R1 will include Row 1,2,3 in R2 join.

这篇关于SQL Server查询，在视图中运行总计，在A列更改时重置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！