在不使用临时表的情况下，对同一列订购并应用运行总数

A representation of my table:

我的桌子的代表:

CREATE TABLE Sales 
    (
     id int identity primary key, 
     SaleAmount numeric(10,2)
    );

DECLARE @i INT;
SELECT @i = 1;
SET NOCOUNT ON
WHILE @i <= 100
BEGIN
    INSERT INTO Sales VALUES (ABS(CHECKSUM(NEWID()))/10000000.0 );
    SELECT @i = @i + 1;
END;
SET NOCOUNT OFF

I need to order my table Sales by SaleAmount and then select all records where a running total of SaleAmount is no greater than X.

我需要按SaleAmount订购我的表，然后选择所有运行的销售总额不大于X的记录。

To do this I'm currently using a temporary table to first sort the records and then selecting records where the running total is less than or equal to X (in this example 10).

为此，我现在使用一个临时表来对记录进行排序，然后选择运行总数小于或等于X的记录(在本例10中)。

CREATE TABLE #TEMP_TABLE 
    (
      ID integer IDENTITY PRIMARY KEY, 
      SaleAmount numeric(10,2)
    );

INSERT INTO #TEMP_TABLE 
(SaleAmount)
SELECT SaleAmount FROM Sales
ORDER BY SaleAmount

SELECT * FROM
  (SELECT
      Id,
      SaleAmount,
      (SaleAmount+COALESCE((SELECT SUM(SaleAmount)
          FROM #TEMP_TABLE b
          WHERE b.Id < a.Id),0))
          AS RunningTotal
    FROM #TEMP_TABLE a) InnerTable
WHERE RunningTotal <= 10

Is there a way in which I can first order my Sales table without the use of a temporary table?

是否有一种方法可以让我不用临时表就可以订购我的销售表?

5 个解决方案

#1

If you are using SQL Server 2012, then you can just use the window function for cumulative sum:

如果您使用的是SQL Server 2012，那么您可以使用窗口函数进行累计求和:

select s.*,
       sum(SaleAmount) over (order by id) as RunningTotal
from Sales s

This is equivalent to the following correlated subquery:

这相当于以下相关子查询:

select s.*,
       (select sum(SalesAmount) from sales s2 where s2.id <= s.id) as RunningTotal
from Sales s

#2

Following Aaron Bertrand's suggestion of using a cursor method :

以下是亚伦伯特兰建议使用光标法:

DECLARE @st TABLE
(
    Id       Int PRIMARY KEY,
    SaleAmount  Numeric(10,2),
    RunningTotal Numeric(10,2)
);

DECLARE
    @Id         INT,
    @SaleAmount  Numeric(10,2),
    @RunningTotal Numeric(10,2) = 0;

DECLARE c CURSOR
    LOCAL STATIC FORWARD_ONLY READ_ONLY
    FOR
    SELECT id, SaleAmount
      FROM Sales
      ORDER BY SaleAmount;

OPEN c;

FETCH NEXT FROM c INTO @Id, @SaleAmount;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @RunningTotal = @RunningTotal + @SaleAmount;

    INSERT @st(Id, SaleAmount,  RunningTotal)
        SELECT @Id, @SaleAmount, @RunningTotal;

    FETCH NEXT FROM c INTO @Id, @SaleAmount;
END

CLOSE c;
DEALLOCATE c;

SELECT Id, SaleAmount, RunningTotal
    FROM @st
    WHERE RunningTotal<=10
    ORDER BY SaleAmount;

This is an increase in code and still requires a table variable. However the improvement in performance is significant.

这是代码的增加，仍然需要一个表变量。然而，性能的改善是显著的。

Credit has to go to Aaron Bertrand for the excellent article on running totals he wrote.

艾伦·伯特兰(Aaron Bertrand)写了一篇关于跑步总成绩的优秀文章，他在这篇文章中获得了赞誉。

#3

One more option with CTE, ROW_NUMBER() ranking function and APPLY() operator

使用CTE、ROW_NUMBER()排序函数和APPLY()运算符还有一个选项

  ;WITH cte AS
   (
    SELECT ROW_NUMBER() OVER(ORDER BY SaleAmount) AS rn, SaleAmount
    FROM Sales s  
    )
    SELECT *
    FROM cte c CROSS APPLY (
                            SELECT SUM(s2.SaleAmount) AS RunningTotal
                            FROM Sales s2
                            WHERE c.SaleAmount >= s2.SaleAmount
                            ) o
    WHERE o.RunningTotal <= 10     

FYI, for avoiding operation of sorting you can use this index:

供参考，为了避免排序操作，您可以使用以下索引:

CREATE INDEX ix_SaleAmount_Sales ON Sales(SaleAmount)

#4

After some research, i believe that what your aiming is not possible, unless using SS2012, or Oracle.

经过一些研究，我相信你的目标是不可能的，除非使用SS2012或者Oracle。

Since your solution seems to work i would advise using a table variable instead of a schema table:

由于您的解决方案似乎有效，我建议使用表变量而不是模式表:

DECLARE @TEMP_TABLE TABLE (
    ID integer IDENTITY PRIMARY KEY,
    SaleAmount numeric(10,2) 
);

INSERT INTO @TEMP_TABLE 
(SaleAmount)
SELECT SaleAmount FROM Sales
ORDER BY SaleAmount

SELECT * FROM
  (SELECT
      Id,
      SaleAmount,
      (SaleAmount+COALESCE((SELECT SUM(SaleAmount)
          FROM @TEMP_TABLE b
          WHERE b.Id < a.Id),0))
          AS RunningTotal
    FROM @TEMP_TABLE a) InnerTable
WHERE RunningTotal <= 10

When testing side-by-side, i found some performance improvements.

当并排测试时，我发现了一些性能改进。

#5

First of all, you are doing a sub-select and then doing a select * from the sub-select. This is unnecessary.

首先，您正在执行子选择，然后在子选择中执行select *。这是不必要的。

SELECT
  Id,
  SaleAmount,
  (SaleAmount+COALESCE((SELECT SUM(SaleAmount)
      FROM #TEMP_TABLE b
      WHERE b.Id < a.Id),0))
      AS RunningTotal
FROM #TEMP_TABLE
WHERE RunningTotal <= 10

Now, the temp table is just a query on the Sales table. There is no purpose to ordering the temporary table because by the rules of SQL, the order in the temporary table does not have to be honored, only the order by clause on the outer query, so

现在，临时表只是销售表上的查询。没有对临时表进行排序的目的，因为根据SQL的规则，临时表中的订单不必进行排序，只需对外部查询中的order by子句进行排序即可

SELECT
  Id,
  SaleAmount,
  (SaleAmount+COALESCE((SELECT SUM(SaleAmount)
      FROM Sales b
      WHERE b.Id < a.Id),0))
      AS RunningTotal
FROM Sales
WHERE RunningTotal <= 10