问题很简单,就是查询付款数据。
数据中同月会出现同一家的多次付款,从而对每次付款的已付值进行多次演算。为此优化程序,数据如下:
ID 月份 单位 付款额
1 201501 A 100
2 201501 B 200
3 201502 A 200
4 201502 A 300
......
100 201510 A 500
101 201510 A 600
102 201510 A 700想得到的查询表
月份 单位 本次付款额 已付款额
201510 A 500 600
201510 A 600 1100
201510 A 700 1700
201510 B 0 200当然单位会很多,不知如此用最快速的查询语句或者存储过程如何解决,谢谢
数据中同月会出现同一家的多次付款,从而对每次付款的已付值进行多次演算。为此优化程序,数据如下:
ID 月份 单位 付款额
1 201501 A 100
2 201501 B 200
3 201502 A 200
4 201502 A 300
......
100 201510 A 500
101 201510 A 600
102 201510 A 700想得到的查询表
月份 单位 本次付款额 已付款额
201510 A 500 600
201510 A 600 1100
201510 A 700 1700
201510 B 0 200当然单位会很多,不知如此用最快速的查询语句或者存储过程如何解决,谢谢
目前用的下面这种方式,但速度很慢,力求更好方法,多谢各位大能!select 单位,
(select sum(付款额) from table a where a.单位=table.单位
and (a.月份<table.月份 or (a.月份=table.月份 and a.ID<table.ID)) )
)from table
where 月份='2015-10'
group by 单位
CREATE TABLE #payDetail (id INT, dt DATE ,dept VARCHAR(10),amount BIGINT)
insert into #payDetail values(1,'20150101','A',100)
insert into #payDetail values(2,'20150101','B',200)
insert into #payDetail values(3,'20150201','A',200)
insert into #payDetail values(4,'20150201','A',300)
insert into #payDetail values(5,'20150301','B',400)
insert into #payDetail values(6,'20150301','B',200)
insert into #payDetail values(7,'20150301','A',200) SELECT a.id,a.dt,a.dept,a.amount,SUM(a.amount) OVER(PARTITION BY a.dept,a.dt ORDER BY a.id)
FROM #payDetail a
ORDER BY a.dept,a.dt,a.id
SET @月份 = 201510;WITH
/* 原始数据
table1(ID,月份,单位,付款额)AS(
SELECT 1,201501,'A',100 UNION ALL
SELECT 2,201501,'B',200 UNION ALL
SELECT 3,201502,'A',200 UNION ALL
SELECT 4,201502,'A',300 UNION ALL
SELECT 100,201510,'A',500 UNION ALL
SELECT 101,201510,'A',600 UNION ALL
SELECT 102,201510,'A',700
),
u(单位) AS (
SELECT 'A' UNION ALL
SELECT 'B'
), */
a AS (-- 本月前合计
SELECT 单位,
SUM(付款额) AS 已付款额
FROM table1
WHERE 月份 < @月份
GROUP BY 单位
)
,b AS (-- 当月明细
SELECT *,
ROW_NUMBER() OVER(PARTITION BY 单位
ORDER BY ID) AS rn
FROM table1
WHERE 月份 = @月份
)
,c AS ( -- 递归
SELECT @月份 AS 月份,
u.单位,
ISNULL(b.付款额,0) AS 本次付款额,
ISNULL(a.已付款额,0) AS 已付款额,
ISNULL(b.rn,0) AS rn
FROM u
LEFT JOIN a
ON a.单位 = u.单位
LEFT JOIN b
ON b.单位 = u.单位
AND b.rn = 1
UNION ALL
SELECT @月份 AS 月份,
c.单位,
ISNULL(b.付款额,0) AS 本次付款额,
ISNULL(c.本次付款额+c.已付款额,0) AS 已付款额,
b.rn
FROM c
JOIN b
ON b.单位 = c.单位
AND b.rn = c.rn + 1
)
SELECT 月份,单位,本次付款额,已付款额
FROM c
ORDER BY 单位, rn
月份 单位 本次付款额 已付款额
----------- ---- ----------- -----------
201510 A 500 600
201510 A 600 1100
201510 A 700 1700
201510 B 0 200
SUM(本次付款) OVER(PARTITION BY 单位
ORDER BY 月份
ROWS BETWEEN UNBOUND PRECEDING
AND CURRENT ROW) AS 阶段付款
FROM ...;