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开始）。也可以将每一个分配记录的数组元素称为“桶”。 
--它有一个参数，用来指定桶数，例如 
---------------------------------------------------------------------------- 
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记录数
从性能上来说，前者高于后者，因为后者会逐行扫描字段中是否有NULL值，有NULL则不加以统计，减少了逻辑读的开销，从而性能达到提升
5. top n With ties 的用法
详见 http://www.cnblogs.com/skynet/archive/2010/03/29/1700055.html
举个例子
select top 1 with ties * from student order by score desc
等价于
select * from student where score=(select top 1 score from student order by score desc)
6. Apply运算符

复制代码代码如下:

 
View Code 
--准备数据 
CREATE TABLE [dbo].[Student]( 
[Id] [int] NULL, 
[Name] [varchar](50) NULL 
) 
go 
INSERT INTO'如快照表复制一份 
a.一旦创建了游标，它就与实际记录相分离并不再维持任何锁 
b.游标就是独立的，不再以任何方式与原始数据相关联 
2）键集驱动的游标（keyset）：需要在一定程度上感知对数据的修改，但不必了解最新发生的所有插入 
a.表必须具有唯一索引 
b.只有键集在tempdb中，而非整个数据集，对整个服务器性能产生有利的影响 
c.能感知到对己是键集一部分的行所做的修改（改删），不能感知新增 
3）动态游标（DYNAMIC） 
a.完全动态，非常敏感，对底层数据做的所有事情都会影响，性能当然也是最差的 
b.它们会带来额外的并发性问题 
c.每发出一次FETCH，都要重建游标 
d.可允许运行没有唯一索引的表中，但弊端会造成SQLSERVER无法追踪它在游标的位置造成死循环，应避免这样使用 
4）快进游标（FAST_FORWARD） 
在许多情况下，FAST_FORWARD游标会隐式转换为其他游标类型

SQL Server 总结复习 （二）

SQL Server 总结复习（二）