SQL Server2005实现累加

来源：互联网发布：淘宝网包邮服务编辑：程序博客网时间：2024/05/20 03:39

原贴：点击打开链接

--注：需要 SQL Server2012 或以上版本才能运行USE tempdbGOIF OBJECT_ID('t') IS NOT NULLDROP TABLE tGOCREATE TABLE t(    月份 int    ,投资代码 NVARCHAR(20)    ,利息收益日期 datetime    ,利息收益      int)GOINSERT INTO t          select  12, 'A', '2017-12-01', 8union all select  12, 'B', '2017-12-01', 10union all select  12, 'A', '2017-12-02', 6union all select  12, 'B', '2017-12-02', 15union all select  12, 'A', '2017-12-03', 10union all select  12, 'B', '2017-12-03', 12union all select  12, 'A', '2017-12-04', 10union all select  12, 'B', '2017-12-04', 10union all select  12, 'A', '2017-12-05', 10union all select  12, 'B', '2017-12-05', 10union all select  12, 'A', '2017-12-06', 20union all select  12, 'B', '2017-12-06', 15union all select  12, 'A', '2017-12-07', 12union all select  12, 'B', '2017-12-07', 10 SELECT     *    ,sum(利息收益) over(partition by 月份,投资代码 order by 利息收益日期 asc) as sum_stepFROM t/*月份          投资代码           利息收益日期            利息收益    sum_step----------- -------------------- ----------------------- ----------- -----------12          A                    2017-12-01 00:00:00.000 8           812          A                    2017-12-02 00:00:00.000 6           1412          A                    2017-12-03 00:00:00.000 10          2412          A                    2017-12-04 00:00:00.000 10          3412          A                    2017-12-05 00:00:00.000 10          4412          A                    2017-12-06 00:00:00.000 20          6412          A                    2017-12-07 00:00:00.000 12          7612          B                    2017-12-01 00:00:00.000 10          1012          B                    2017-12-02 00:00:00.000 15          2512          B                    2017-12-03 00:00:00.000 12          3712          B                    2017-12-04 00:00:00.000 10          4712          B                    2017-12-05 00:00:00.000 10          5712          B                    2017-12-06 00:00:00.000 15          7212          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 twhere sum_step>=50group by 月份,投资代码/*月份          投资代码           利息收益日期            利息收益Total----------- -------------------- ----------------------- -----------12          A                    2017-12-06 00:00:00.000 6412          B                    2017-12-05 00:00:00.000 57*/

--下面的代码在 SQL Server2005 上运行无误USE tempdbGOIF OBJECT_ID('t') IS NOT NULLDROP TABLE tGOCREATE TABLE t(月份 int,投资代码 NVARCHAR(20),利息收益日期 datetime,利息收益  int)GOINSERT INTO t          select  12, 'A', '2017-12-01', 8union all select  12, 'B', '2017-12-01', 10union all select  12, 'A', '2017-12-02', 6union all select  12, 'B', '2017-12-02', 15union all select  12, 'A', '2017-12-03', 10union all select  12, 'B', '2017-12-03', 12union all select  12, 'A', '2017-12-04', 10union all select  12, 'B', '2017-12-04', 10union all select  12, 'A', '2017-12-05', 10union all select  12, 'B', '2017-12-05', 10union all select  12, 'A', '2017-12-06', 20union all select  12, 'B', '2017-12-06', 15union all select  12, 'A', '2017-12-07', 12union all select  12, 'B', '2017-12-07', 10;with cte as(SELECTrow_number() over(partition by 月份,投资代码 order by 利息收益日期 asc) as rid ,*FROM t),cte2 as (select * from cte where rid=1union allselect 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 利息收益Totalfrom cte2 where 利息收益>=50group by 月份,投资代码/*月份          投资代码           利息收益日期            利息收益Total----------- -------------------- ----------------------- -----------12          A                    2017-12-06 00:00:00.000 6412          B                    2017-12-05 00:00:00.000 57*/

阅读全文

0 0