SQL Server 排名函数与PARTITION BY详解（复习篇）

80 浏览量更新于2024-08-31 收藏 99KB PDF 举报

"SQL Server 是一种广泛使用的数据库管理系统，本文档是对 SQL Server 的进一步总结和复习，旨在帮助学习者巩固和理解 SQL Server 的核心功能。主要涵盖以下几个关键知识点： 1. 排名函数与 PARTITION BY: 在 SQL Server 中，`ROW_NUMBER()` 函数用于对查询结果按照指定的列进行排序，并为每一行分配一个唯一的行号，从 1 开始递增。例如，通过 `OVER (ORDER BY B.SCORE DESC)` 子句，我们可以对 '语文' 成绩表中的学生按分数降序排列。同时，`ORDER BY a.Id` 可以在生成行号后，再按学生ID进行一次重新排序。 2. RANK() 函数：与 ROW_NUMBER() 类似，RANK() 也是用于排名，但它在遇到相同排名值时，会保持相同的顺序，而不会跳过下一个排名。这意味着如果有多个学生的分数相同，他们都将获得相同的排名。这在处理多维度比较时非常有用。 3. 分组统计后的排名：当需要在分组后应用排名函数时，通常使用 Common Table Expression (CTE) 或嵌套查询来首先计算每个组的汇总信息，然后将这个结果与原始表连接，利用 ROW_NUMBER() 或 RANK() 对每个组内的记录进行排名。 4. PARTITION BY 语句：这个关键词用于在排序或分组时将数据分为多个分区，每个分区独立进行排序或计算。这对于按某个字段（如学科）进行细分并分别进行排名或统计很有帮助，比如在上述的 `SELECT` 查询中，如果要按学科分组后再排名，可以在 `OVER` 子句中加入 `PARTITION BY scorename`。通过这些例子，学习者可以掌握如何在 SQL Server 中灵活运用排名函数，以便于数据分析、报表生成或满足特定的业务需求。熟练掌握这些功能对于在实际项目中编写高效查询至关重要。"

SQL Server 总结复习总结复习（二）（二）

SQL Server 总结复习，学习sqlserver的朋友可以参考下

1. 排名函数与排名函数与PARTITION BY

复制代码代码如下:

--所有数据

SELECT * FROM dbo.student AS a INNER JOIN dbo.ScoreTB AS b ON a.Id = b.stuid

WHERE scorename = '语文'

-------------------------------------------

--ROW_NUMBER() 的使用生成列从1开始依次增加

-------------------------------------------

SELECT ROW_NUMBER() OVER (ORDER BY B.SCORE DESC) AS ROWNUMBER ,A.NAME, B.SCORE, a.Id

FROM dbo.student AS a INNER JOIN dbo.ScoreTB AS b ON a.Id = b.stuid

WHERE scorename = '语文'

--也可以在后面再加一个order by，则表示前面生成后的全部列又被以最后的列重新排列（排名列值不变）

SELECT ROW_NUMBER() OVER (ORDER BY B.SCORE DESC) AS ROWNUMBER ,A.NAME, B.SCORE, a.Id

FROM dbo.student AS a INNER JOIN dbo.ScoreTB AS b ON a.Id = b.stuid

WHERE scorename = '语文' ORDER BY a.Id

--要在分组统计后使用排名函数，则先进行分组，用cte或嵌套查询表整出结果集，再用row_number函数处理

WITH b AS

(

SELECT stuid, SUM(score) AS score FROM ScoreTB GROUP BY stuid

)

SELECT * ,ROW_NUMBER() OVER (ORDER BY b.score desc) AS rownumer

FROM dbo.student AS a INNER JOIN b ON a.id = b.stuid

----------------------------------------------------------------------------

--RANK() 用法与ROW_NUMER函数想同，只是在出现order by同级时，排名会设置成一样，而下一个会根据之前的记录数生

成序号

--例如前面三个是一样的，那么都是1，下一个则是4，示例略

----------------------------------------------------------------------------

--DENSE_RANK() 密集排名用法与ROW_NUMER、RANK函数相同，只是在生成序号时是连续的，而rank函数生成的序号有

可能不连续

--例如前面三个是一样的，那么都是1，下一个则是2，示例略

----------------------------------------------------------------------------

--ntile函数可以对序号进行分组处理。这就相当于将查询出来的记录集放到指定长度的数组中，每一个数组元素存放一定数量

的记录。

--为每条记录生成的序号就是这条记录所有的数组元素的索引（从1开始）。也可以将每一个分配记录的数组元素称为“桶”。

--它有一个参数，用来指定桶数，例如

----------------------------------------------------------------------------

SELECT ntile(2) OVER (ORDER BY B.SCORE DESC) AS GROUPID ,A.NAME, ISNULL(B.SCORE,0) SCORE, a.Id

FROM dbo.student AS a LEFT JOIN dbo.ScoreTB AS b ON a.Id = b.stuid AND scorename = '语文'

--------------------------------------------------------------------------

--PARTITION BY 类似于向排名函数应用一个group by，分组后对每一个组单独排名

--------------------------------------------------------------------------

--统计各个学科的排名依次为：

SELECT RANK() OVER (PARTITION BY b.scorename ORDER BY B.SCORE DESC) AS ROWNUMBER,b.scorename,

b.score, A.NAME, a.Id FROM dbo.student AS a INNER JOIN dbo.ScoreTB AS b ON a.Id = b.stuid ORDER BY

SCORENAME

2. TOP 新用法新用法

复制代码代码如下:

DECLARE @num INT = 101

SELECT TOP (@num) * FROM Student ORDER BY Id --必须用括号括起来

SELECT TOP (@num) percent * FROM Student ORDER BY Id --只接受float并且1-100之间的数，如果传入其他则会报错

3. group by all 字段字段 / group by 字段字段

前者有点像left join ,right join的感觉，两者的主要区别体现在有where条件被过滤的聚合函数，会重新抓取出来放入查询的数

据表中，只是聚合函数会根据返回值的类型用默认值0或者NULL来代替聚合函数的返回值。

当然从效率上来说，后者优于前者，就像inner join 优于left join一样

4. count(*)/count(0) 与与 count(字段字段)

如果查询出来的字段中没有NULL值，那么俩种查询条件无任何区别，如果有NULL，后者统计出来的记录则是总记录数 -

NULL记录数

下载后可阅读完整内容，剩余3页未读，立即下载

weixin_38695773

粉丝: 11
资源: 956

SQL Server 排名函数与PARTITION BY详解（复习篇）

数据库面试题SQL+ORACLE

sql server 期末复习总结

SQl Server 复习范围总结

SQL Server 总结复习(一)

sql server期末复习

SQL server 2005复习题

sql server 2000复习试题

SQL Server数据库复习题

sql server2000复习题

sql server2005复习题

最新资源