34,590
社区成员
发帖
与我相关
我的任务
分享
;WITH table1(账号, 存取日期, 发生额, 余额) as
(
select 'A','2015-10-06',100000.00, 100000.00 union all
select 'A','2015-10-15',-50000.00, 50000.00 union all
select 'B','2015-10-03',20000.00, 20000.00 union all
select 'B','2015-10-21',40000.00, 60000.00 union all
select 'A','2015-11-06',180000.00, 230000.00 union all
select 'A','2015-11-15',-100000.00,130000.00 union all
select 'B','2015-11-03',20000.00, 80000.00 union all
select 'B','2015-11-22',40000.00, 120000.00
)
, b AS (
SELECT DISTINCT 账号
FROM table1
)
,c AS ( -- 日历
SELECT convert(nvarchar(10),DateAdd(day,number,'2015-10-01'), 120) 日期
FROM master..spt_values
WHERE type = 'p'
AND number < 61
)
,d AS (
SELECT b.账号,
c.日期
FROM b
JOIN c
ON 1=1
)
,e AS
(
select d.账号, d.日期, isnull(f.余额, 0) as 余额 from d
OUTER APPLY (
SELECT TOP 1 *
FROM table1 t
WHERE t.账号 = d.账号
AND t.存取日期 <= d.日期
ORDER BY t.存取日期 DESC
) f
)
SELECT
账号, MONTH(日期) 月份,
convert(decimal(18,2),AVG(余额)) as 日均余额
FROM e
GROUP BY MONTH(日期), 账号
PS: 你不是已经有过类似的发帖么。。。
http://bbs.csdn.net/topics/391846027
跟这帖应该差不多吧。