SQL Server2005实现累加

原贴: 点击打开链接
--注:需要 SQL Server2012 或以上版本才能运行
USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL
DROP TABLE t
GO
CREATE TABLE t(
    月份 int
    ,投资代码 NVARCHAR(20)
    ,利息收益日期 datetime
    ,利息收益      int
)
GO
INSERT INTO t
          select  12, 'A', '2017-12-01', 8
union all select  12, 'B', '2017-12-01', 10
union all select  12, 'A', '2017-12-02', 6
union all select  12, 'B', '2017-12-02', 15
union all select  12, 'A', '2017-12-03', 10
union all select  12, 'B', '2017-12-03', 12
union all select  12, 'A', '2017-12-04', 10
union all select  12, 'B', '2017-12-04', 10
union all select  12, 'A', '2017-12-05', 10
union all select  12, 'B', '2017-12-05', 10
union all select  12, 'A', '2017-12-06', 20
union all select  12, 'B', '2017-12-06', 15
union all select  12, 'A', '2017-12-07', 12
union all select  12, 'B', '2017-12-07', 10
 
SELECT 
    *
    ,sum(利息收益) over(partition by 月份,投资代码 order by 利息收益日期 asc) as sum_step
FROM t
/*
月份          投资代码           利息收益日期            利息收益    sum_step
----------- -------------------- ----------------------- ----------- -----------
12          A                    2017-12-01 00:00:00.000 8           8
12          A                    2017-12-02 00:00:00.000 6           14
12          A                    2017-12-03 00:00:00.000 10          24
12          A                    2017-12-04 00:00:00.000 10          34
12          A                    2017-12-05 00:00:00.000 10          44
12          A                    2017-12-06 00:00:00.000 20          64
12          A                    2017-12-07 00:00:00.000 12          76
12          B                    2017-12-01 00:00:00.000 10          10
12          B                    2017-12-02 00:00:00.000 15          25
12          B                    2017-12-03 00:00:00.000 12          37
12          B                    2017-12-04 00:00:00.000 10          47
12          B                    2017-12-05 00:00:00.000 10          57
12          B                    2017-12-06 00:00:00.000 15          72
12          B                    2017-12-07 00:00:00.000 10          82
*/
 
select  月份
        ,投资代码
        ,min(利息收益日期) as [利息收益日期]
        ,min(sum_step) as [利息收益Total]
    from (
        SELECT 
            *
            ,sum(利息收益) over(partition by 月份,投资代码 order by 利息收益日期 asc) as sum_step
        FROM t
    ) as t
where sum_step>=50
group by 月份,投资代码
/*
月份          投资代码           利息收益日期            利息收益Total
----------- -------------------- ----------------------- -----------
12          A                    2017-12-06 00:00:00.000 64
12          B                    2017-12-05 00:00:00.000 57
*/

--下面的代码在 SQL Server2005 上运行无误
USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL
DROP TABLE t
GO
CREATE TABLE t(
	月份 int
	,投资代码 NVARCHAR(20)
	,利息收益日期 datetime
	,利息收益	  int
)
GO
INSERT INTO t
          select  12, 'A', '2017-12-01', 8
union all select  12, 'B', '2017-12-01', 10
union all select  12, 'A', '2017-12-02', 6
union all select  12, 'B', '2017-12-02', 15
union all select  12, 'A', '2017-12-03', 10
union all select  12, 'B', '2017-12-03', 12
union all select  12, 'A', '2017-12-04', 10
union all select  12, 'B', '2017-12-04', 10
union all select  12, 'A', '2017-12-05', 10
union all select  12, 'B', '2017-12-05', 10
union all select  12, 'A', '2017-12-06', 20
union all select  12, 'B', '2017-12-06', 15
union all select  12, 'A', '2017-12-07', 12
union all select  12, 'B', '2017-12-07', 10

;with cte as(
	SELECT
		row_number() over(partition by 月份,投资代码 order by 利息收益日期 asc) as rid 
		,*
	FROM t
)
,cte2 as (
	select * from cte where rid=1
	union all
	select a.rid,a.月份,a.投资代码,a.利息收益日期,a.利息收益+b.利息收益 as 利息收益 
	from cte as a inner join cte2 b on a.rid=b.rid+1 and a.月份=b.月份 and a.投资代码=b.投资代码
)
select 
	月份
	,投资代码
	,min(利息收益日期) as 利息收益日期
	,min(利息收益) as 利息收益Total
from cte2 
where 利息收益>=50
group by 月份,投资代码
/*
月份          投资代码           利息收益日期            利息收益Total
----------- -------------------- ----------------------- -----------
12          A                    2017-12-06 00:00:00.000 64
12          B                    2017-12-05 00:00:00.000 57
*/
相关文章

相关标签/搜索