求个算法 某字段累加直到结果等于我需要的数量

[梁超]

比如 参数传5000
则从表中按时间排序 把数量的字段逐条相加 直到计算总量满足5000 为止.

[哥眼神纯洁不]

来点数据看看

[Tiger_Zhao]

SELECT *
  FROM (SELECT T1.id, T1.时间, T1.数量,
              (SELECT SUM(数量) FROM 表1 T2 WHERE T2.时间 < T1.时间) 前累计数量
          FROM 表1 T1
       ) T
 WHERE 前累计数量<5000 
   AND (前累计数量+数量)>=5000

知道了那一条达到 5000，以及这条的时间，剩下的想怎么处理随意

[专注or全面]

为了简单起见，用了ID替代时间
create table t1
(
	id int,
	account int
)

insert into t1 values (1,RAND()*100)
insert into t1 values (2,RAND()*100)
insert into t1 values (3,RAND()*100)
insert into t1 values (4,RAND()*100)
insert into t1 values (5,RAND()*100)
insert into t1 values (6,RAND()*100)
insert into t1 values (7,RAND()*100)
insert into t1 values (8,RAND()*100)
insert into t1 values (9,RAND()*100)
insert into t1 values (10,RAND()*100)



select * from t1
id          account
----------- -----------
1           90
2           46
3           90
4           32
5           41
6           66
7           59
8           67
9           98
10          92

(10 行受影响)


select *
  from t1
 where id <=
       (select MIN(id)
          from (select A2.id,
                       (select sum(A1.account) from t1 A1 where A1.id <= A2.Id) as total
                  from t1 A2) t
         where total >= 500)

id          account
----------- -----------
1           90
2           46
3           90
4           32
5           41
6           66
7           59
8           67
9           98

(9 行受影响)

[专注or全面]

sqlserver2014的分析函数可以直接求出来那个要统计的值 支持sum()over(partition by 1 oder by datetime)这样子

[梁超]

是这个意思: 表内存在5条数据 id count createdate 1 100 2014-1-1 2 1500 2014-1-2 3 3000 2014-1-3 4 300 2014-1-4 5 5000 2014-1-5 我需要总量5000 计算的话按照日期升序 来相加 计算完前4条数据的和为4900 然后第五条数据取100 最后5条数据变为 id count createdate 1 0 2014-1-1 2 0 2014-1-2 3 0 2014-1-3 4 0 2014-1-4 5 4900 2014-1-5

[Tiger_Zhao]

IF OBJECT_ID('表1') IS NOT NULL
  DROP TABLE 表1
GO

CREATE TABLE 表1 (id int, count int, createdate datetime)
GO

INSERT INTO 表1 
SELECT 1, 100,'2014-1-1' UNION ALL
SELECT 2,1500,'2014-1-2' UNION ALL
SELECT 3,3000,'2014-1-3' UNION ALL
SELECT 4, 300,'2014-1-4' UNION ALL
SELECT 5,5000,'2014-1-5' UNION ALL
SELECT 6,1000,'2014-1-6' -- 加一条更新无关的进行对比
GO

DECLARE @总量 INT 
SET @总量 = 5000

IF OBJECT_ID('表1') IS NOT NULL
  DROP TABLE #T

SELECT *
  INTO #T
  FROM (SELECT T1.id, T1.createdate, T1.count,
               (SELECT ISNULL(SUM(count),0) FROM 表1 T2 WHERE T2.createdate < T1.createdate) 前累计数量
          FROM 表1 T1
       ) T
 WHERE 前累计数量 < @总量

-- 全取光的
UPDATE 表1 
   SET count = 0
  FROM 表1, #T
 WHERE 表1.id = #T.id
   AND (#T.前累计数量 + #T.count) <= @总量

-- 有节余的
UPDATE 表1 
   SET count = (#T.前累计数量 + #T.count) - @总量
  FROM 表1, #T
 WHERE 表1.id = #T.id
   AND (#T.前累计数量 + #T.count) > @总量

SELECT * FROM 表1

id          count       createdate
----------- ----------- ----------
1           0           2014-01-01
2           0           2014-01-02
3           0           2014-01-03
4           0           2014-01-04
5           4900        2014-01-05
6           1000        2014-01-06

[Tiger_Zhao]

IF OBJECT_ID('#T') IS NOT NULL
DROP TABLE #T

[Tiger_Zhao]

临时表的判断的确写错了。
一般程序调用时会关连接，是否需要删除看实际情况。

通常会有产品编号作为条件，参与运算的记录不会很多。
如果要运算成百上千条记录才能凑满 5000，那么方案设计就有问题。

[shinger126]

引用 7 楼 Tiger_Zhao 的回复:

IF OBJECT_ID('#T') IS NOT NULL DROP TABLE #T

临时表好像不能这么判断的吧

[shinger126]

LZ的需求貌似是先进先出算法是吧。数据量大的话，用游标比较好，数据量小用6楼的方法吧

[chenglongwei]

此需求可以用游标解决，但是如果数量大了就非常慢，我之前做过一个项目（项目中的需求和这个类似，但是比这个要复杂，涉及多表运算）主表500w条数据运算用游标需要16个小时，但是后来使用子查询加case when的方法直接查询计算即可，每计算一次只需要几分钟即可 根据上述问题可以通过以下语句来实现，但是只是针对这个表面需求去实现， 相信现实的需求一定比这个复杂，并且还要注意如果时间存在相同的时候会不会出现问题，是否需要row_number()之类的函数先生成一个唯一id来替代时间判断。

IF OBJECT_ID('t1') IS NOT NULL
  DROP TABLE t1
GO
 
CREATE TABLE t1 (id int, count int, createdate datetime)
GO
 
INSERT INTO t1 
SELECT 1, 100,'2014-1-1' UNION ALL
SELECT 2,1500,'2014-1-2' UNION ALL
SELECT 3,3000,'2014-1-3' UNION ALL
SELECT 4, 300,'2014-1-4' UNION ALL
SELECT 5,5000,'2014-1-5' UNION ALL
SELECT 6,1000,'2014-1-6' -- 加一条更新无关的进行对比
GO

--子查询+case when实现
SELECT id,COUNT, ROW_NUMBER()
结果=CASE WHEN (SELECT SUM(count) FROM t1 AA WHERE AA.createdate<=t1.createdate)<=5000 THEN 0 ELSE count-(5000-(SELECT SUM(count) FROM t1 AA WHERE AA.createdate<t1.createdate)) END
FROM t1

[chenglongwei]

多了一个东西没有删除

--子查询+case when实现
SELECT id,COUNT, 
结果=CASE WHEN (SELECT SUM(count) FROM t1 AA WHERE AA.createdate<=t1.createdate)<=2400 THEN 0 ELSE count-(2400-(SELECT SUM(count) FROM t1 AA WHERE AA.createdate<t1.createdate)) END
FROM t1