分组迭代求和SQL语句怎么写

Question

回款表回款日期                客户id       回款金额
20150101                1                  3
20140202                1                  5
20150802                1                  2
20130802                1                  1
.......此处省略还有很多客户的
回款统计表
客户id           金额
1                      5
......此处还有很多客户的，但每个客户就对应唯一的金额
查询回款统计表里客户1的金额是5，那么去回款表里去按时间从后向前找相加满足5的行，查询出如下结果
回款日期                客户id       回款金额
20150101                1                  3
20150802                1                  2
.....每个客户的都要查询如果回款统计表的金额不能正好是回款表里行的相加的话，可以多查询一行， 例：如果表2 客户1金额是7  查询出如下结果
回款日期                客户id       回款金额
20140202                1                  5
20150101                1                  3
20150802                1                  2

Answer 1

WITH /* 测试数据
回款日期,客户id,回款金额)AS(
    SELECT '2015-01-01',1,3 UNION ALL
    SELECT '2014-02-02',1,5 UNION ALL
    SELECT '2015-08-02',1,2 UNION ALL
    SELECT '2013-08-02',1,1 UNION ALL
    SELECT '2015-06-01',2,4 UNION ALL
    SELECT '2015-07-01',2,5 UNION ALL
    SELECT '2015-08-01',2,6
)
,回款统计表(客户id,金额)AS(
    SELECT 1,5 UNION ALL
    SELECT 2,6
), */
a AS(
    SELECT *,
           ROW_NUMBER() OVER(PARTITION BY 客户id ORDER BY 回款日期 DESC) rn
      FROM 回款表
)
,c AS (
    SELECT a.*,
           b.金额 - a.回款金额 AS 剩余金额
      FROM a
      JOIN 回款统计表 b
        ON b.客户id = a.客户id
     WHERE rn = 1
    UNION ALL
    SELECT a.*,
           c.剩余金额 - a.回款金额 AS 剩余金额
      FROM a
      JOIN c
        ON c.客户id = a.客户id
       AND c.rn + 1 = a.rn
     WHERE c.剩余金额 > 0
)
    SELECT 回款日期,客户id,回款金额
      FROM c
  ORDER BY 客户id, rn
回款日期        客户id    回款金额
---------- ----------- -----------
2015-08-02           1           2
2015-01-01           1           3
2015-08-01           2           6

Answer 2

忘了加金额不等的数据了，再测试一下
WITH /* 测试数据
回款表(回款日期,客户id,回款金额)AS(
    SELECT '2015-05-01',3,3 UNION ALL
    SELECT '2015-06-01',3,4 UNION ALL
    SELECT '2015-07-01',3,5 UNION ALL
    SELECT '2015-08-01',3,6
)
,回款统计表(客户id,金额)AS(
    SELECT 3,12
), */
...
回款日期        客户id    回款金额
---------- ----------- -----------
2015-08-01           3           6
2015-07-01           3           5
2015-06-01           3           4

Answer 3

我的是SQL seerver 2000 不支持ROW_NUMBER()  函数 

Answer 4

不早说
CREATE TABLE #a(
    回款日期 datetime,
    客户id int,
    回款金额 int,
    rn int identity
)
INSERT INTO #a (回款日期,客户id,回款金额)
    SELECT *
      FROM 回款表
  ORDER BY 客户id, 回款日期 DESC--SELECT * FROM #aCREATE TABLE #b (
    客户id int,
    金额 int,
    first_rn int
)INSERT INTO #b
    SELECT b.客户id,
           b.金额,
           MIN(#a.rn)
      FROM 回款统计表 b
      JOIN #a
        ON #a.客户id = b.客户id
  GROUP BY b.客户id, b.金额--SELECT * FROM #bCREATE TABLE #c(
    回款日期 datetime,
    客户id int,
    回款金额 int,
    rn int,
    回款前金额 int
)INSERT INTO #c
    SELECT a.*,
           b.金额 AS 回款前金额
      FROM #a a
      JOIN #b b
        ON b.客户id = a.客户id--SELECT * FROM #cUPDATE #c
   SET #c.回款前金额 = 回款前金额 - cs.回款金额
  FROM #c, 
       (
            SELECT c1.客户id,
                   c1.rn,
                   SUM(c2.回款金额) 回款金额
              FROM #c c1
              JOIN #c c2
                ON c1.客户id = c2.客户id
               AND c1.rn > c2.rn
          GROUP BY c1.客户id, c1.rn
       ) cs
 WHERE #c.客户id = cs.客户id
   AND #c.rn = cs.rn    SELECT 回款日期,客户id,回款金额
      FROM #c
     WHERE 回款前金额 > 0
  ORDER BY 客户id, rn
回款日期                     客户id    回款金额
----------------------- ----------- -----------
2015-08-02 00:00:00.000           1           2
2015-01-01 00:00:00.000           1           3
2015-08-01 00:00:00.000           2           6
2015-08-01 00:00:00.000           3           6
2015-07-01 00:00:00.000           3           5
2015-06-01 00:00:00.000           3           4