SQL Server 语句：每月之前的金额累计

比如，表结果如下：

CREATE TABLE [dbo].[sales](
 [id] [int] NOT NULL,
 [mymoney] [int] NULL,
 [month] [int] NULL
) ON [PRIMARY]

 

内容：

id    mymoney month

1  100   1
2  300   4
3  150   2
4  400   2
5  100   2
6  120   3
7  300   3
8  800   5

 

求出，每月之前发生所有mymoney累计。

 

WITH    temp
          AS ( SELECT   [myroder] ,
                        SUM(mymoney) AS mymoney
               FROM     sales
               GROUP BY myroder
             )
    SELECT  [myroder] ,
            mymoney ,
            ( SELECT    SUM(mymoney)
              FROM      temp AS a
              WHERE     a.myroder <= temp.myroder
            ) AS 累计
    FROM    temp

 

 

结果：

 

month mymoney   累计
1  100   100
2  650   750
3  420   1170
4  300   1470
5  800   2270
      

 

 

如果字段是日期函数，则：

 

WITH    temp
          AS ( SELECT   YEAR(mydatetime) AS year ,
                        MONTH(mydatetime) AS month ,
                        DAY(mydatetime) AS day ,
                        SUM(mymoney) AS mymoney
               FROM     sales
               GROUP BY DAY(mydatetime) ,
                        MONTH(mydatetime) ,
                        YEAR(mydatetime)
             )
    SELECT  year ,
            [month] ,
            day ,
            mymoney ,
            ( SELECT    SUM(mymoney)
              FROM      temp AS a
              WHERE     a.year <  temp.year
                        OR ( a.year = temp.year
                             AND a.month <  temp.month
                           )
                        OR ( a.year = temp.year
                             AND a.month = temp.month
                             AND a.day <= temp.day
                           )
            ) AS 累计
    FROM    temp
    ORDER BY year ,
            month ,
            day