SQL SERVER: Open window function SUM () over () data statistics in one case using

Due to the previous period of stomach pain, the blog Park blog has been stopped more than one months or so. In recent days, stomach trouble finally slightly improved, decided to re-write blog post.

A few days ago, a friend just asked me about the SQL statement, roughly the original table has two columns, respectively, the month, the month sales, and the need for a SQL statement to achieve the statistics of each month and the current month before the sales and the month. No, it's still not clear, the following figure is shown.

   

SQL Test Table Script

DECLARE @Temp Table (  ID INT,---month  moneydata Float  ---amount) insert into  @TEMPSELECT 1,100 UNION allselect< C7/>2,200 Union allselect  3,300 Union allselect  4,400 Union allselect  5,500 Union allselect  6,600 UNION Allselect  7,600

A self-connected

SELECT  a.id, SUM (b.moneydata) from  @Temp A INNER JOIN @Temp BON a.id>=b.id GROUP by a.id------focus on the on condition, via self-connect a.ID >= b.ID, can obtain the required data, and then through group by, sum can be summed

　　

Two open Window function

SELECT  ID, Moneydata,   sum (moneydata) over () as ' total sales ', sum (moneydata) over (PARTITION by ID) as ' monthly Sales ', SUM (money Data) over (order by ID ASC)  as ' month + previous month sales ' from @Temp----here, using the window function is simpler, however, where SUM () over (ORDER by ID ASC) can achieve the above Function

　　

For the specific use of the window function, refer to Microsoft MSDN Official API documentation, and finally, recommend a window function book: SQL SERVER 2012 based Windows functions.

Reference: Https://msdn.microsoft.com/zh-cn/library/ms173454.aspx

SQL SERVER: Open window function SUM () over () data statistics in one example using