SQL Server 总结复习 （二）

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开始）。也可以将每一个分配记录的数组元素称为“桶”。