T-SQL:开窗函数的常见应用

深藏阁楼爱情的钟 2022-07-24 03:28 255阅读 0赞

# 为每条数据显示聚合信息（聚合函数()over()） #

查询每个工资小于5000元的员工信息（城市以及年龄），并且在每行中都显示所有工资小于5000元的员工个数

select City,Age,count(*)over() from Person where Salary<5000;

<table> 
 <thead> 
  <tr> 
   <th>City</th> 
   <th align="center">Age</th> 
   <th align="right"></th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>Beijing</td> 
   <td align="center">20</td> 
   <td align="right">5</td> 
  </tr> 
  <tr> 
   <td>Londonu</td> 
   <td align="center">21</td> 
   <td align="right">5</td> 
  </tr> 
  <tr> 
   <td>London</td> 
   <td align="center">23</td> 
   <td align="right">5</td> 
  </tr> 
  <tr> 
   <td>Beijing</td> 
   <td align="center">22</td> 
   <td align="right">5</td> 
  </tr> 
  <tr> 
   <td>NewYork</td> 
   <td align="center">24</td> 
   <td align="right">5</td> 
  </tr> 
 </tbody> 
</table>

# 为每条数据提供分组的聚合函数结果（聚合函数()over(partion by 字段)as 别名） #

查询每一个人员的信息以及所属城市的人员数

select Name,City,Age,Salary,count(*)over(parttion byCity) from Person;

<table> 
 <thead> 
  <tr> 
   <th>Name</th> 
   <th>City</th> 
   <th>Age</th> 
   <th align="center"></th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>Tom</td> 
   <td>Beijing</td> 
   <td>20</td> 
   <td align="center">4</td> 
  </tr> 
  <tr> 
   <td>Jim</td> 
   <td>Beijing</td> 
   <td>21</td> 
   <td align="center">4</td> 
  </tr> 
  <tr> 
   <td>Merry</td> 
   <td>Beijing</td> 
   <td>23</td> 
   <td align="center">4</td> 
  </tr> 
  <tr> 
   <td>Bill</td> 
   <td>Beijing</td> 
   <td>22</td> 
   <td align="center">4</td> 
  </tr> 
  <tr> 
   <td>Swing</td> 
   <td>NewYork</td> 
   <td>24</td> 
   <td align="center">3</td> 
  </tr> 
  <tr> 
   <td>Guo</td> 
   <td>NewYork</td> 
   <td>24</td> 
   <td align="center">3</td> 
  </tr> 
  <tr> 
   <td>John</td> 
   <td>NewYork</td> 
   <td>20</td> 
   <td align="center">3</td> 
  </tr> 
  <tr> 
   <td>Jerry</td> 
   <td>London</td> 
   <td>24</td> 
   <td align="center">2</td> 
  </tr> 
  <tr> 
   <td>Danny</td> 
   <td>London</td> 
   <td>25</td> 
   <td align="center">2</td> 
  </tr> 
 </tbody> 
</table>

# 与排名函数一起使用（row number()over(order by 字段)as 别名） #

row number()函数用于计算一行在结果集中的行号。  
在分页查询中，若根据id来限制每页的行数，每页数据经过一系列修改后，id可能不再连续，若再根据id来限制每页行数，显示的结果可能就会和实际预想的不同。  
则可添加一列，来代表行数，他是连续的。

select num , stuId , stuName , stuSex , stuBirthdate , stuStudydate , stuAddress , stuEmail , stuPhone , stuIsDel , stuInputtime , classId from ( select row_number() over(order by stuid) as num , stuId , stuName , stuSex , stuBirthdate , stuStudydate , stuAddress , stuEmail , stuPhone , stuIsDel , stuInputtime , classId from TestDataBase..Student where stuIsDel = 0) as t where t.num between (10-1)*9+1 and 9*10;

每页m条，第n页  
每一页的第一条就是上一页加一条，m\*(n-1)+1