文章详情

  • 游戏榜单
  • 软件榜单
关闭导航
热搜榜
热门下载
热门标签
php爱好者> php文档>T-SQL在MSSQL2005/2008中新增的几个功能

T-SQL在MSSQL2005/2008中新增的几个功能

时间:2011-01-11  来源:reni

  • Ranking函数

          MSSQL2005新增了四个排名函数,ROW_NUMBER, RANK, DENSE_RANK, NTILE。利用这些函数可以有效地分析数据以及向查询的结果行提供排序值。

          下面举例说明。先准备数据: 

     

    测试表和测试数据
     1 CREATE TABLE [Test]
    2 (
    3 [StudentID] [bigint] NOT NULL,
    4 [ClassID] [bigint] NOT NULL,
    5 [TestScore] [decimal](4, 1) NOT NULL
    6 ) ON [PRIMARY]
    7 GO
    8
    9 INSERT INTO [Test]
    10 VALUES (100001,100,90),
    11 (100002,100,85.5),
    12 (100003,100,80),
    13 (100004,100,80),
    14 (100005,100,74),
    15 (101001,101,94),
    16 (101002,101,85.5),
    17 (101003,101,85.5)
    18 GO
    19

     

         下面是四个排序函数统一的示例和结果,可以做一比较。下面的小节来会逐一描述每个函数。

      

    Ranking示例
    1 SELECT *, 
    2 ROW_NUMBER() OVER (ORDER BY TestScore DESC) as RN,
    3 RANK() OVER (ORDER BY TestScore DESC) as R,
    4 DENSE_RANK() OVER (ORDER BY TestScore DESC) as DR,
    5 NTILE(3) OVER (ORDER BY TestScore DESC) as N3
    6 FROM [TEST]
    7 GO
    8

      

      

    ROW_NUMBER

        行号函数。用来生成数据行在结果集中的序号

        语法:

    ROW_NUMBER( ) OVER ([<partition_by_clause>] <order_by_clause>)

        示例:

     

    1 SELECT *, ROW_NUMBER() OVER (ORDER BY TestScore DESC) as RN
    2 FROM [Test]
    3

      

        可以利用ROW_NUMBER函数非常便利的实现分页功能,例如:

     

    1 SELECT *, ROW_NUMBER() OVER (ORDER BY TestScore DESC) as RN 
    2 FROM [TEST]
    3 WHERE RN BETWEEN 6 AND 10
    4

     

    RANK

        排序函数。必须配合over函数,且排序字段值相同的行号一样,同时隐藏行号会占位。

        语法:

    RANK() OVER ([<partition_by_clause>] <order_by_clause>)

        示例:

     

    1 SELECT *, RANK() OVER (ORDER BY TestScore DESC) as R
    2 FROM [Test]
    3

     

         还可以利用partition进行分组排序,例如对每个班级分别按成绩排序:

     

    1 SELECT *, RANK() OVER (PARTITION BY ClassID ORDER BY TestScore DESC) as R
    2 FROM [Test]
    3

     

     

    DENSE_RANK

        紧凑排序函数。与RANK函数不同的是,当排序字段值相同导致行号一样时,同时隐藏行号不占位。

        语法:

    DENSE_RANK ( ) OVER ([<partition_by_clause>] <order_by_clause>)

        示例:

     

    1 SELECT *, DENSE_RANK() OVER ( ORDER BY TestScore DESC) as DR
    2 FROM [Test]
    3

     

        从第一个结果集中可以看到,DENSE_RANK得到的行号是3,3,3,4,4,5,而RANK函数得到的3,3,3,6,6,8。

     

    NTILE

        分区排序函数。NTILE函数需要一个参数N,这个参数支持bigint。这个函数将结果集等分成N个区,并按排序字段将已排序的记录依次轮流放入各个区内。最后每个区内会从1开始编号,NTILE函数返回这个编号。

        语法:

    NTILE (integer_expression) OVER ([<partition_by_clause>]< order_by_clause>)

        示例:

     

    1 SELECT *, NTILE(3) OVER ( ORDER BY TestScore DESC) as N3
    2 FROM [Test]
    3

       

        从第一个结果集可以看到被分成了3个区,因为8条记录不能被平分,所以第3个区只有2条记录。排序的记录被依次按第1->2->3->1区的顺序分配,最终函数的返回是每个区内记录的序号。 

        这个函数可以用来按权值均分记录。      

         

  • CTE

        Common Table Expression,通用表表达式,是一个可以由定义语句引用临时表命名的结果集。只须定义CTE一次,即可多次引用它。可以在所有的CURD操作中使用CTE。CTE可以省去创建和维护临时表或者表变量的工作。

        语法:

    WITH <cte_alias>(<column_aliases>)

    AS

    (

    <cte_query>

    )

        根据第1节的数据提供一个示例,获取每个班级第一名的数据: 

     

    1 ;WITH Test_RN
    2 AS
    3 (
    4 SELECT StudentID, ClassID, TestScore,RANK() OVER (PARTITION BY ClassID ORDER BY TestScore DESC) as RN
    5 From Test
    6 )
    7 Select * FROM Test_RN WHERE RN=1
    8

     

     

        需要特别提出的是CTE更为强大的一个功能,可以在递归中使用。

        递归 CTE 定义至少必须包含两个 CTE 查询定义,一个定位点成员(只能调用一次),一个递归成员(可以反复调用,直到查询不再返回行),使用 UNION ALL连接成一个单独的CTE。

      

  • Merge 

        MERGE语法是MSSQL2008新增的功能,将源表与目标表关联,并根据结果对目标表执行插入、更新或删除操作。常规操作需要自行比对结果,利用一系列的IF-ELSE和CASE-WHEN,根据逻辑判断执行不同的操作。MERGE语法则提供了内建的支持,更加灵活。   

        语法:

    MERGE

        [ TOP ( expression ) [ PERCENT ] ]

        [ INTO ] <target_table> [ WITH ( <merge_hint> ) ] [ [ AS ] table_alias ]

        USING <table_source>

        ON <merge_search_condition>

        [ WHEN MATCHED [ AND <clause_search_condition> ]

            THEN <merge_matched> ] [ ...n ]

        [ WHEN NOT MATCHED [ BY TARGET ] [ AND <clause_search_condition> ]

            THEN <merge_not_matched> ]

        [ WHEN NOT MATCHED BY SOURCE [ AND <clause_search_condition> ]

            THEN <merge_matched> ] [ ...n ]

        [ <output_clause> ]

        [ OPTION ( <query_hint> [ ,...n ] ) ]

    ;

     

        将第1节的TEST表作为目标表,准备一个源表:

     

    测试表和测试数据
     1 CREATE TABLE [NewTest]
    2 (
    3 [StudentID] [bigint] NOT NULL,
    4 [TestScore] [decimal](4, 1) NOT NULL
    5 ) ON [PRIMARY]
    6 GO
    7
    8 INSERT INTO [NewTest]
    9 VALUES (100001,90),
    10 (100003,85.5),
    11 (100004,77),
    12 (101001,90),
    13 (101002,90),
    14 (101003,85.5),
    15 (101004,88)
    16 GO
    17

     

        比较源表和目标表发现,有的记录两表中一致;有的两表中部分一致;有的只存在源表中;有的只存在目标表中。我们需要以源表为标准来更新目标表。

     

    Merge示例
    1 MERGE Test
    2 USING NewTest ON Test.StudentID = NewTest.StudentID
    3 WHEN MATCHED THEN UPDATE SET Test.TestScore = NewTest.TestScore
    4 WHEN NOT MATCHED THEN INSERT VALUES (NewTest.StudentID, NewTest.StudentID/1000, NewTest.TestScore)
    5 WHEN NOT MATCHED BY SOURCE THEN DELETE
    6 ;
    7

     

        如果再次查看目标表,会发现已按要求更新。

     

     

        还可以带OUTPUT命令同时获得MERGE命令中Inserted或Deleted的记录。

      OUTPUT $action, Deleted.*, Inserted.* 

     

     

     

     
相关阅读 更多 +
排行榜 更多 +
mirrox模组(玩家自制)手机版下载

mirrox模组(玩家自制)手机版下载

休闲益智 下载
集装箱模拟器手机版下载安装

集装箱模拟器手机版下载安装

模拟经营 下载
哔咔漫画app下载免费2025

哔咔漫画app下载免费2025

浏览阅读 下载