Edit - I am using MSSQL Server 2005 - SP4 (9.0.5000)

I have 3 columns in a table - Customer ID, Order Number, Payment Date, and Payment. When a user makes a payment, it logs data using the Customer ID, increments the order number (so the first payment is 1, second payment is 2, etc), and has a date and payment amount.

我在一个表中有3列——客户ID、订单号、付款日期和付款。当用户付款时，它使用客户ID记录数据，增加订单号(因此第一次付款是1，第二次付款是2，等等)，并有日期和付款金额。

I only want to query all the first payments made until the total payment amount reaches a threshold/condition of 100.

我只想查询所有第一次支付，直到总支付金额达到阈值/条件为100。

First I tried just working with sum() to see if something would just magically pop into my head - but total sums are all I know how to do:

首先，我试着用sum()来计算，看看是否有什么东西会神奇地出现在我的脑海中——但我只知道怎么做:

select CustomerID, Order, sum(Payment) as FirstFullPayment
from #temp
group by CustomerID

Then I tried making 2 different temporary tables - #up for unfinished payments and #fp for finished payments. My thinking is that I can put the confirmed >100 payments into #fp, and put the confirmed unfinished payments into #up with something like this:

我的想法是，我可以将已确认的>100款项放入#fp中，并将已确认的未完成款项放入#up中，如下所示:

select * into #fp
from #temp
where Order = 1 and Payment >= 100

select * into #up
from #temp
where Order = 1 and Payment < 100

Then I could sequentially join #up and #temp where Order = 2, 3, 4, etc., add the Payments in #up until they are >= 100, then insert them into #fp

然后我可以按顺序加入#up和#temp，其中Order = 2、3、4等，在#up中添加支付，直到它们是>= 100，然后将它们插入#fp中

The only thing is I know this is a very bad and roundabout method, and there must be a better and simpler way to do this! Thanks in advance!

唯一的事情是我知道这是一个非常糟糕和迂回的方法，必须有一个更好和更简单的方法去做!提前谢谢!

3 个解决方案

#1

You can use running sums and select the row you by your criteria: <=100

您可以使用运行和并根据您的条件选择行:<=100

select top 1 * from 
    (
    select CustomerID,  
                (
                SELECT SUM(b.payment)
                           FROM #temp b
                           WHERE a.customerid=b.customerID and  b.[order] <= a.[order]) as FirstFullPayment
    from #temp a
    --where customerid=yourCustomerId
    )runningsums
where runningsums.FirstFullPayment<=100
order by runningsums.FirstFullPayment desc

#2

Here's another method using common table expressions (which were introduced with SQL 2005):

下面是另一种使用公共表表达式的方法(SQL 2005中引入的):

;WITH cteBaseline
 as (--  For each customer, for each order, get sum of payments for all
     --  orders less than or equal to "this" order, where the total is
     --  within the desired range
     select
        te.CustomerId
       ,te.Order
       ,sum(subset.Payment) TotalPayment
      from #temp te
       inner join #temp subset
        on subset.CustomerId <= te.CustomerId
      group by
        te.CustomerId
       ,te.Order
      having sum(subset.Payment) < 100  --  Make this a parameter to control the "breakpoint"
     )
--  Get the "last" row
select
   cte.CustomerId
  ,cte.Order
  ,cte.TotalPayment
 from cteBaseline cte
  inner join (--  get the "last" row, the one with the largest TotalPayment
              select
                 CustomerId
                ,max(Order)
               from cteBaseline
               group by CustomerId) xx
   on xx.CustomerId = cte.CustomerId
    and xx.Order = cte.Order

You'd want to double-check the syntax. Also, depending on table size and indexes this might perform poorly, as there's a lot of joining going on.

您需要再次检查语法。此外，根据表大小和索引的不同，它的性能可能会很差，因为会有很多连接。

#3

I'm assuming that none of the payments can be negative so that the cumulative sum will also be an increasing series (monotonic). Here's one way to do it if you want the cumulative total and order number at the point it crosses the threshold:

我假设所有支付都不能为负，因此累积和也将是一个递增的级数(单调)。这里有一种方法，如果你想要累积的总数和订单数量超过阈值时:

select CustomerID, min(OrderNumber), min(CumulativePayments)
from
(
    select
        CustomerID, OrderNumber,
        (
            select sum(t2.Payment) from T t2
            where t2.CustomerID = t.CustomerID and t2.OrderNumber <= t.OrderNumber
        ) as CumulativePayments
   from T t
) cp
where CumulativePayments >= 100
group by CustomerID

EDIT: If you need all the data and rows up to the point of the threshold:

编辑:如果您需要所有的数据和行到阈值点:

select * from T t
where OrderNumber <=
(
    select min(OrderNumber)
    from
    (
        select
            OrderNumber,
            (
                select sum(t3.Payment) from T t3
                where t3.CustomerID = t2.CustomerID and t3.OrderNumber <= t2.OrderNumber
            ) as CumulativePayments
        from T t2
        where t2.CustomerID = t.CustomerID
    ) cp
    where CumulativePayments >= 100
)

These approaches don't rely on row_number() or sum() over (...) or top n...order by.

这些方法不依赖于row_number()或sum()除以(…)或top n…order by。

#1