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

 

• Ranking函数

      MSSQL2005新增了四个排名函数，ROW_NUMBER, RANK, DENSE_RANK, NTILE。利用这些函数可以有效地分析数据以及向查询的结果行提供排序值。
      下面举例说明。先准备数据： 
 

CREATE TABLE [Test]
(
    [StudentID] [bigint] NOT NULL,
    [ClassID] [bigint] NOT NULL,
    [TestScore] [decimal](4, 1) NOT NULL
) ON [PRIMARY]
 GO

 INSERT INTO [Test] 
    VALUES (100001,100,90),
              (100002,100,85.5),
              (100003,100,80),
              (100004,100,80),
              (100005,100,74),
              (101001,101,94),
              (101002,101,85.5),
              (101003,101,85.5)
 GO
 

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

SELECT *, 
     ROW_NUMBER() OVER (ORDER BY TestScore DESC) as RN,
     RANK() OVER (ORDER BY TestScore DESC) as R,
     DENSE_RANK() OVER (ORDER BY TestScore DESC) as DR,
     NTILE(3) OVER (ORDER BY TestScore DESC) as N3
 FROM [TEST]
 GO
 

  

  
ROW_NUMBER
      行号函数。用来生成数据行在结果集中的序号
      语法：
ROW_NUMBER( ) OVER ([<partition_by_clause>] <order_by_clause>)
      
      可以利用ROW_NUMBER函数非常便利的实现分页功能，例如：
 

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

 
RANK
      排序函数。必须配合over函数,且排序字段值相同的行号一样,同时隐藏行号会占位。
      语法：
RANK() OVER ([<partition_by_clause>] <order_by_clause>)
    
      还可以利用partition进行分组排序，例如对每个班级分别按成绩排序：
 

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

 

 
DENSE_RANK
      紧凑排序函数。与RANK函数不同的是，当排序字段值相同导致行号一样时,同时隐藏行号不占位。
      语法：
DENSE_RANK ( ) OVER ([<partition_by_clause>] <order_by_clause>)
    
      从第一个结果集中可以看到，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>)
 
      这个函数可以用来按权值均分记录。      
      从第一个结果集可以看到被分成了3个区，因为8条记录不能被平分，所以第3个区只有2条记录。排序的记录被依次按第1->2->3->1区的顺序分配，最终函数的返回是每个区内记录的序号。   
         

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

      语法：
WITH <cte_alias>(<column_aliases>)
AS
(
<cte_query>
)
      根据第1节的数据提供一个示例，获取每个班级第一名的数据： 
 

;WITH Test_RN
 AS
(
      SELECT StudentID, ClassID, TestScore,RANK() OVER (PARTITION BY ClassID ORDER BY TestScore DESC) as RN
      From Test
)
 Select * FROM Test_RN WHERE RN=1

 
 
      需要特别提出的是CTE更为强大的一个功能，可以在递归中使用。
      递归 CTE 定义至少必须包含两个 CTE 查询定义，一个定位点成员（只能调用一次），一个递归成员（可以反复调用，直到查询不再返回行），使用 UNION ALL连接成一个单独的CTE。
      我们举一个示例，有一张部门表：
 

 CREATE TABLE [Dept]
 (
    [DeptID] [bigint] NOT NULL,
     [DeptName] [nvarchar](50) NOT NULL,
     [ParentDeptID] [bigint] NOT NULL
 ) ON [PRIMARY]
  GO
 
  INSERT INTO [SSIP-SSO].[dbo].[Dept]
      VALUES
            (1, '集团', 0),
            (2, '总公司', 1),
            (3, '技术部', 2),
            (4, '开发组', 3),
            (5, '测试部', 3),
            (6, '财务部', 2),
            (7, '分公司', 1),
            (8, '工程部', 7),
            (9, '设备组', 8),
            (10, '客服部', 7)
  GO

     
      我们需要获取“总公司”下所有部门的信息：
 

  ;WITH Dept_Head
   AS
  (
      SELECT DeptID, DeptName, ParentDeptID 
      FROM Dept
      WHERE DeptName = '总公司'
      UNION ALL
      SELECT Dept.DeptID, Dept.DeptName, Dept.ParentDeptID
      FROM Dept, Dept_Head
      WHERE Dept.ParentDeptID = Dept_Head.DeptID
  )
   Select * FROM Dept_Head

  

    

• 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表作为目标表，准备一个源表：

     

  CREATE TABLE [NewTest]
  (
        [StudentID] [bigint] NOT NULL,
        [TestScore] [decimal](4, 1) NOT NULL
  ) ON [PRIMARY]
   GO
  
   INSERT INTO [NewTest] 
       VALUES (100001,90),
                    (100003,85.5),
                    (100004,77),
                    (101001,90),
                    (101002,90),
                    (101003,85.5),
                    (101004,88)
   GO

     

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

   

  MERGE Test
        USING NewTest 
        ON Test.StudentID = NewTest.StudentID
        WHEN MATCHED  --记录两表匹配
          THEN UPDATE SET Test.TestScore = NewTest.TestScore
        WHEN NOT MATCHED --记录在目标表不存在
          THEN INSERT VALUES (NewTest.StudentID,NewTest.StudentID/1000, NewTest.TestScore) 
        WHEN NOT MATCHED BY SOURCE  --记录在源表中不存在
          THEN DELETE
  ;

   

     

     

      还可以带OUTPUT命令同时获得MERGE命令中更新的记录。

      OUTPUT $action, Deleted.*, Inserted.*