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 dbo.Student VALUES （1, "张三"） INSERT INTO dbo.Student VALUES （2, "李斯"） INSERT INTO dbo.Student VALUES （3, "王五"） INSERT INTO dbo.Student VALUES （4, "神人"） go CREATE TABLE [dbo].[scoretb]（ [stuId] [int] NULL, [scorename] [varchar]（50） NULL, [score] INT NULL ） go INSERT INTO [scoretb] VALUES （1,"语文",22） INSERT INTO [scoretb] VALUES （1,"数学",32） INSERT INTO [scoretb] VALUES （1,"外语",42） INSERT INTO [scoretb] VALUES （2,"语文",52） INSERT INTO [scoretb] VALUES （2,"数学",62） INSERT INTO [scoretb] VALUES （2,"外语",72） INSERT INTO [scoretb] VALUES （3,"语文",82） INSERT INTO [scoretb] VALUES （3,"数学",92） INSERT INTO [scoretb] VALUES （3,"外语",72） --创建表值函数 CREATE FUNCTION [dbo].[fGetScore]（@stuid int） RETURNS @score TABLE （ [stuId] [int] NULL, [scorename] [varchar]（50） NULL, [score] INT NULL ） as BEGIN INSERT INTO @score SELECT stuid,scorename,score FROM dbo.scoretb WHERE stuId = @stuid RETURN; END GO --开始使用 SELECT A.id,A.name,B.scorename,B.score FROM [Student] A CROSS APPLY [dbo].[fGetScore]（A.Id） B --相当于inner join效果 SELECT A.id,A.name,B.scorename,B.score FROM [Student] A OUTER APPLY [dbo].[fGetScore]（A.Id） B --相当于left join效果 --而不能这样使用 --SELECT A.id,A.name,B.scorename,B.score FROM [Student] A -- INNER JOIN [dbo].[fGetScore]（A.Id） B ON A.Id = B.stuid -- SELECT A.id,A.name,B.scorename,B.score FROM [Student] A -- INNER JOIN （SELECT * FROM [dbo].[fGetScore]（A.Id）） B ON A.Id = B.stuid
7. INTERSECT和EXCEPT运算符 EXCEPT 只包含excpet关键字左边而且右边的结果集中不存在的那些行 INTERSECT 只包含两个结果集中都存在的那些行 往往EXISTS关键字可以代替上面的关键字，并且从性能中可以看到比他们更好，但EXCEPT/INTERSECT更便于阅读和直观。还是建议从性能更优入手。 8. 索引提高查询效率的原理 索引与EXISTS运算符在处理方式上很像，它们都可以在找到匹配值后立即退出查询运行，从而提高了查询性能 9. 表变量与临时表 主要区别： 1表变量不写日志，没有统计信息，频繁更改不会造成存储过程重新编译，不能建索引和统计信息，但是可以建立主键，变通实现索引查找，表变量不只是在内存中操作，数据量大的情况也会写tempdb，即物理磁盘的IO操作。 2.事务回滚对表变量无效（原因没有统计信息） 一般来说，数据量大，临时结果集需要和其他表二次关联用临时表 数据量小，单独操作临时结果集用表变量 10. 脚本和批处理 Go不是一条T-SQL命令，他只能被编译工具Management Studio, SQLCMD识别，如果用第三方工具，不一定支持GO命令。例如ADO.NET,ADO。 11. SQLCMD的运用 SQLCMD -Usa -Psa -Q "SELECT * FROM TESTDB.dbo.mytable" SQLCMD -Usa -Psa -i testsql.sql 运行文件里的SQL语句 12. EXEC 使用说明 在执行过EXEC之后，可以使用类似@@ROWCOUNT这样的变量查看影响行数；不能在EXEC的参数中，针对EXEC字符串运行函数，例如cast（XX AS VARCHAR），对于EXEC的参数，只能用字符串相加，或者是整体的字符串。 13. WAITFOR 的含义 WAITFOR TIME <"TIME"> 定时执行; WAITFOR DELAY <"TIME"> 延迟执行 14. 存储过程 总结 1）用TRY/CATCH 替代 @@ERROR这种更科学，其一@@ERROR没有TRA/CATCH直观，其二遇到错误级别在11-19的错误，错误会使运行直接中断，导致@@ERROR判断错误与否无效。 2）使用RAISERROR 抛错 WITH LOG，当严重级别大于等于19时，需要使用这个选项 WITH SETERROR，使其重写@@ERROR值，方便外部调用 WITH NOWAIT 立刻将错误通知给客户端 15. 游标的复习 游标主要部分包括：1）声明 2）打开 3）使用或导航 4）关闭 5）释放 嵌套使用游标示例 复制代码 代码如下: DECLARE BillMsgCursor CURSOR FOR SELECT TypeNo,TabDetailName FROM dbo.BillType OPEN BillMsgCursor DECLARE @TypeNo CHAR（5） DECLARE @DetailName VARCHAR（50） FETCH NEXT FROM BillMsgCursor INTO @TypeNo,@DetailName WHILE @@FETCH_STATUS = 0 BEGIN DECLARE @DataFieldName VARCHAR（50） DECLARE ColumnName CURSOR FOR SELECT name FROM syscolumns WHERE id = OBJECT_ID（@DetailName） OPEN ColumnName FETCH NEXT FROM ColumnName INTO @DataFieldName PRINT "单据编号:" + @TypeNo WHILE @@FETCH_STATUS = 0 BEGIN PRINT "ListDetailDataFiled.Add（"""+@DataFieldName+"""）;" FETCH NEXT FROM ColumnName INTO @DataFieldName END CLOSE ColumnName DEALLOCATE ColumnName FETCH NEXT FROM BillMsgCursor INTO @TypeNo,@DetailName END CLOSE BillMsgCursor DEALLOCATE BillMsgCursor
易网时代-编程资源站