SQL使用自连接计算客户订单的运行总数

Question

所以我知道这已经做得有点死，但我真的很挣扎。在工作中，我有SQL Server 2008，我被要求做一个总运行，所以没有窗口函数为我。这意味着我必须以老一套的方式去做，所以为了学习这一点，我使用了AdventureWorks 2012数据库，并尝试在totaldue上运行销售中的salesorderid与customerid。salesorderheader表。相关栏目为:

salesorderid int not null
customerid int not null
totaldue money not null

到目前为止我写的是:

select soh1.salesorderid
, soh1.customerid
, soh1.totaldue
, sum(soh2.totaldue) 'running_total'
from sales.salesorderheader soh1
inner join sales.salesorderheader soh2 on soh1.totaldue <= soh2.totaldue
                                       and soh1.customerid = soh2.customerid
group by soh1.customerid, soh1.salesorderid, soh1.totaldue
order by soh1.customerid, soh1.salesorderid;

得到以下内容(前10行):

salesorderid    customerid  totaldue    runningtotal
43793           11000       3756.989    3756.989
51522           11000       2587.8769   9115.1341
57418           11000       2770.2682   6527.2572
43767           11001       3729.364    3729.364
51493           11001       2674.0227   6403.3867
43736           11002       3756.989    3756.989
51238           11002       2535.964    8966.0143
53237           11002       2673.0613   6430.0503
43701           11003       3756.989    3756.989
51315           11003       2562.4508   8993.9155
57783           11003       2674.4757   6431.4647

我觉得我很接近了——我注意到，当给定的customerid只有两个销售订单时，运行的总数计算正确。然而，当它是3或更多时，它就会变成垃圾。

我查看了customerid = 11000连接的中断，以了解为什么第二个和第三个orderid(51522和57418)不能正确计算。我运行了以下代码:

select soh1.salesorderid
, soh1.customerid
, soh1.totaldue
, soh2.salesorderid
, soh2.customerid
, soh2.totaldue
from sales.salesorderheader soh1
inner join sales.salesorderheader soh2 on soh1.totaldue <= soh2.totaldue
                                          and soh1.customerid = soh2.customerid
where soh1.customerid = 11000
order by soh1.customerid, soh1.salesorderid;

我得到这个:

salesorderid  customerid  totaldue   salesorderid  customerid  totaldue
43793         11000       3756.989   43793         11000       3756.989
51522         11000       2587.8769  43793         11000       3756.989
51522         11000       2587.8769  51522         11000       2587.8769
**51522       11000       2587.8769  57418         11000       2770.2682**
57418         11000       2770.2682  57418         11000       2770.2682
57418         11000       2770.2682  43793         11000       3756.989

我可以看到第4行中的问题，它在那里引入了我不希望的行，然后对于salesorderid 57418，它缺少一行。我怀疑问题是与我的连接，但我不能找出逻辑，将带来正确的行。

我已经要求能够使用SQL 2012或更高版本，但没有骰子。请帮助!

Answer 1

不应该对客户和销售订单id而不是totaldue进行自连接吗?

select soh1.salesorderid
, soh1.customerid
, soh1.totaldue
, sum(soh2.totaldue) 'running_total'
from sales.salesorderheader soh1
inner join sales.salesorderheader soh2 on soh2.salesorderid <= soh1.salesorderid
                                   and soh2.customerid = soh1.customerid
group by soh1.customerid, soh1.salesorderid, soh1.totaldue
order by soh1.customerid, soh1.salesorderid;

Answer 2

或者您可以尝试CTE

;with grouped as
(
    select *, ROW_NUMBER() OVER (PARTITION BY customerid ORDER BY salesorderid) as groupId 
    from sales.salesorderheader
), cte as
(
    select *, totaldue as runningtotal from grouped where groupId = 1
    union all
    select grouped.*, grouped.totaldue + cte.runningtotal
    from grouped inner join cte on 
        grouped.groupId - 1 = cte.groupId 
        and grouped.customerid = cte.customerid
)
select * from cte order by customerid, salesorderid

Answer 3

使用此代码:

select soh1.salesorderid
, soh1.customerid
, soh1.totaldue
,(select  sum(soh2.totaldue) from sales.salesorderheader soh2      
  where soh1.totaldue <= soh2.totaldue 
  and  soh1.customerid = soh2.customerid) as 'Running Totoal'
from sales.salesorderheader soh1
where soh1.customerid = 11000
order by soh1.customerid, soh1.salesorderid