SQL Server 总结复习(二)

来源:互联网 时间:1970-01-01

1. 排名函数与PARTITION BY

View Code
--所有数据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


相关阅读:
Top