SQL大圣之路笔记——SQL over 字句实例

 1 --显示每个部门3名员工的信息，按照薪资的降序排列
 2 
 3  create table Emp
 4  (
 5     id int identity(1,1) primary key not null,
 6     name nvarchar(50),
 7     salary int,
 8     depno int 
 9  )
10 
11  insert Emp VALUES('tester1',100,10)
12  insert Emp VALUES('tester2',200,10)
13  insert Emp VALUES('tester3',300,10)
14  insert Emp VALUES('tester4',400,10)
15  insert Emp VALUES('tester5',500,10)
16 
17  insert Emp VALUES('tester6',600,20)
18  insert Emp VALUES('tester7',500,20)
19  insert Emp VALUES('tester8',500,20)
20  insert Emp VALUES('tester9',900,20)
21  insert Emp VALUES('tester10',1000,20)
22 
23  insert Emp VALUES('tester11',200,30)
24  insert Emp VALUES('tester12',500,30)
25  insert Emp VALUES('tester13',1300,30)
26  insert Emp VALUES('tester14',300,30)
27  insert Emp VALUES('tester15',1500,30)
28 
29 
30   SELECT * FROM 
31     ( SELECt * ,row_number()over(partition by depno ORDER BY salary DESC)as depAsc FROM Emp  ) as t1
32   where t1.depAsc<4
33