高级T_SQL语法(2005)

排名函数:

  1. RANK() 返回结果集的分区内每行的排名,FIELD为排名依据。行的排名是相关行之前的排名数加1.例如有两个并列第一,那么后一个将排名第三,而不是第二。

    语法:RANK() OVER([PARTITION BY clause]<ORDER BY clause>)

    示例:

    select *, rank() over (partition by ClassID order by Mark DESC )as rank from dbo.Student

    结果:

  2. DENSE_RANK():返回结果集分区中行的排名。在排名中没有任何间断。行的排名等于所讨论的行之前的所有排名数加1.例如有两个并列第一,那么下一个将排名第二,而不是第三。

    语法:DENSE_RANK() OVER([PARTITION BY clause]<ORDER BY clause>)

    示例:

    select *, dense_rank() over (partition by ClassID order by MarkDESC ) as rank from dbo.Student

    结果:

  3. NTILE():将有序分区内的行分发到指定数目的组中。各个组由编号。,编号从1开始,对于每个行,NITLE()将返回此行所属组的编号。

    语法:NTILE (int_expressions) OVER([PARTITION BY clause]<ORDER BY clause>)

    int_expressions:正整数常量表达式。用于指定每个分区必须被划分成的组数。

    示例:

    select *,NTILE(4) over ( order by Mark DESC ) as NewCLASS fromdbo.Student

     

  4. ROW_NUMBER()一般用于数据库分页。返回结果集分区内行的序列号。每个分区的第一行从1开始。

    语法:ROW_NUMBER () OVER([PARTITION BY clause]<ORDER BY clause>)

    示例:

    select *, ROW_NUMBER() over (partition by ClassID order by MarkDESC ) as rank from dbo.Student

    结果:

     

异常处理:SQL2005中对t-sql实现了类似c#语言中的异常处理机制。

语法:BEGIN TRY { sql_statement|statement_block}

END TRY

NEGIN CATCH{ sql_statement|statement_block }

END CATCH

此模块捕捉所有严重级大于10但不终止数据库连接的错误。此处try catch不能宽约多个处理,特不能跨越多个t-sql语句块。例如不能跨越两个BEGIN END语句块,也不能跨越IF ELSE语句块。

此处,需要注意的是,catch并不会将消息传递给应用程序,所以catch中必须显式调用select结果集或RAISEROR和PRINT语句之类的机制执行此操作。

TRY CATCH可以嵌套。也可以从存储过程或者触发器捕捉未处理的错误。当然存储过程之类的也可以使用try catch。不能使用goto语句输入try catch语句块,也不可以跳转到同一个try catch块内的某个标签或离开try catch模块。不能在用户定义函数中使用try catch构造。

Catch块内用来捕捉错误异常信息的函数:

ERROR_NUMBER() 返回错误号

ERROR_SERVERITY:返回严重性

ERROR_STATE:错误状态号

ERROR_PRODURE()返回出现错误的存储过程或者触发器的名称

ERROR_LINE()导致错误的例程中的行号

ERROR_MESSAGE()错误信息的完整文本

如果没有,则返回空值。

 

捕捉不倒的错误信息:

严重新小于等于10的错误

严重性高于等于20且终止SQLSERVERDATABASEENGINE任务处理的错误

需要关注的消息,如客户端终端请求或者客户端连接终端

系统管理员使用KILL语句终止会话时

 

以下类型错误发生界别与TRY catch构造的执行等级相通,则catch不会处理:

编译错误

语句级重新编译过程中出现的错误

 

APPLY操作符号:可以为实现查询操作的外部表表达式返回每个行调用表值函数。表值函数作为右输入,外部表表达式作为左输入。通过对右输入求值来获得左输入每一行的计算结果,生成的行作为最终输出。APPLY运算符生成的列表是左输入中的列集后跟右输入返回列的列表。

两种形式:

CROSS APPLY:仅返回外部表中通过表值函数生成结果集的行

OUTER APPLY:既返回生成结果集的行,也返回不生成结果集的行,不生成结果集的行,表值函数列中的值为NULL。

语法:

function apply([thisObj : Object [,argArray : { Array | arguments }]])

 

PIVOT、UNPIVOT运算符:对结果集进行旋转,这样列变成行,行变成列。这成为旋转数据或者创建交叉报表。

示例:

select ID,Name,[1] as aaa,[2] as bbb,[3] as ccc,[4] as ddd

from dbo.ProductSale

PIVOT (

Sum (Sale)

for Quarter in ([1],[2],[3],[4])

) as dddd

结果:

SQL2005如果对上面这句话出现错误提示,那么请设置sql2005兼容性。设置兼容性的步骤:

选中具体数据库->右击->属性->选项->兼容级别设置,设置为最高的90就可以了。

UNPIVOT为相反操作,将如上面的结果这样的数据表变回去。

 

公用表达式CTE:公用表达式(CTE)是在select、insert、update或delete语句执行过程中暂时存储的结果集。利用CTE可使用递归查询,并可以通过取代临时表或试图来简化逻辑。

语法: with expression_name{ ( column_name[,…..,n])} as (CTE_QUERY_DEGINATION)

使用CTE与ROW_NUMBER()函数结合用于数据库分页:

示例:

with cc as

(

    select *, ROW_NUMBER()

    over

        (partition by ClassID order by Math DESC )

        as RowID

    from dbo.Student

)

select * from cc where RowID>2 and RowID<5

结果:

 

引用自身从而创建递归CTE。递归CTE是一个重复执行初始CTE以返回数据子集,指导获取完整结果集的公用表表达式。

递归由三个元素组成:

  1. 例程调用。递归CTE的第一个调用包括一个或者多个由UNION ALL、UNION、EXCEPT或INTERSECT运算符连接的CTE_QUERY_DEFINITIONS。由于这些查询定义了CTE的基准结果集。所以成为定位点成员。
  2. 例程的递归调用。递归调用包括一个或者多个由引用CTE本身CTE本身的UNION ALL运算符连接的CTE——QUERY——DEFINITIONS。这些查询被定义为递归成员。
  3. 终止检查。隐式的,当一个调用未返回行时,递归停止。

 

from:http://www.cnblogs.com/BLoodMaster/archive/2010/03/22/1691632.html