34,587
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE #a(
回款日期 datetime,
客户id int,
回款金额 int,
rn int identity
)
INSERT INTO #a (回款日期,客户id,回款金额)
SELECT *
FROM 回款表
ORDER BY 客户id, 回款日期 DESC
--SELECT * FROM #a
CREATE 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 #b
CREATE 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 #c
UPDATE #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
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
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