create table Employee
(
    ID INT PRIMARY KEY
    ,Name VARCHAR(20)
    ,GroupName VARCHAR(20)
    ,Salary INT
) INSERT into Employee VALUES(1, '小明', '開發(fā)部', 8000), (4, '小張', '開發(fā)部', 7600), (5, '小白', '開發(fā)部', 7000), (8, '小王', '財(cái)務(wù)部', 5000), (9, null, '財(cái)務(wù)部', NULL), (15, '小劉', '財(cái)務(wù)部', 6000), (16, '小高', '行政部', 4500), (18, '小王', '行政部', 4000), (23, '小李', '行政部', 4500), (29, '小吳', '行政部', 4700)
;

select  *
        ,SUM(Salary) OVER(PARTITION BY Groupname) 每個(gè)組的總工資
        ,SUM(Salary) OVER(PARTITION BY groupname ORDER BY ID) 每個(gè)組的累計(jì)總工資
        ,SUM(Salary) OVER(ORDER BY ID) 累計(jì)工資
        ,SUM(Salary) OVER() 總工資
from    Employee

SUM(Salary) OVER (PARTITION BY Groupname)

SUM(Salary) OVER (PARTITION BY Groupname ORDER BY ID)

SUM(Salary) OVER (ORDER BY ID)

SUM(Salary) OVER ()

select  *
        ,ROW_NUMBER() OVER (PARTITION BY ClassName ORDER BY SCORE DESC) 班內(nèi)排序
        ,ROW_NUMBER() OVER (ORDER BY SCORE DESC) AS 總排序
from    Scores
;

select  ROW_NUMBER() OVER (ORDER BY SCORE DESC) AS [RANK]
        ,*
from    Scores
;

select  RANK() OVER (ORDER BY SCORE DESC) AS [RANK]
        ,*
from    Scores
;

select  *
        ,NTILE(1) OVER (ORDER BY SCORE DESC) AS 分區(qū)后排序
from    Scores
;
select  *
        ,NTILE(2) OVER (ORDER BY SCORE DESC) AS 分區(qū)后排序
from    Scores
;
select  *
        ,NTILE(3) OVER (ORDER BY SCORE DESC) AS 分區(qū)后排序
from    Scores
;

select  *
        ,SUM(Salary) OVER(PARTITION BY groupname ORDER BY ID ROWS between CURRENT ROW and 5 FOLLOWING) 從當(dāng)前行開始后5行的和
from    Employee