08 msbi学习资料sql高级语法 天善智能

1、天善智能请输入文字S Q L 高级语法2012-5-22目录CTE定义及应用窗口函数，分区函数应用For XML Path应用Pivot(行转列)应用Merge应用动态SQL执行结果赋值给变量ON 后面加And,WHERE区别，及Except语法天善智能CTE公用表表达式(CTE) 具有一个重要的优点，那就是能够引用其自身，从而创建递归CTE。递归CTE 是一个重复执行初始CTE 以返回数据子集直到获取完整结果集的公用表表达式。在SQL Server 2005 中，当某个查询引用递归CTE 时，它即被称为“递归查询”。递归查询通常用于返回分层数据，例如：

2、显示某个组织图中的雇员或物料方案（其中父级产品有一个或多个组件，而那些组件可能还有子组件，或者是其他父级产品的组件）中的数据。天善智能CTE作用递归CTE 可以极大地简化在SELECT、CREATE VIEW INSERT、UPDATE、DELETE 或语句中运行递归查询所需的代码。在SQL Server 的早期版本中，递归查询通常需要使用临时表、游标和逻辑来控制递归步骤流。with cte as(select ROW_NUMBER()over(partition by Num,Name order by Getdate() as RN from A)delete from cte where

3、 RN1天善智能CTE实现递归天善智能窗口函数窗口函数和聚集函数一样都是对定义的行集（组）进行聚集，但是不像聚集一样只返回 一个值，窗口函数可以为每个组返回多个值， 执行聚集的行组是窗口(因此称为窗口函数)。窗口函数是在聚集函数的基础上加了一个over()，所有的聚集函数都可以利用这种方式转换成窗口函数。窗口函数是最后才执行的，在order by 之前,where和group by之后天善智能分区函数Partition By分区子句：可以根据partition by子句定义行的分区或组，以完成聚集，如果使用空括号，那么整个结果集就是分区，窗口函数将对它进行聚集计算， 可以把Partition

4、By看成是移动的Group By， 可以用Partition By对定义的行组计算聚集(当遇到新的组时复位)，并返回每个值(每个组中的成员)，而不是用一个组表示表中这个值的所有实例。天善智能窗口函数与分区函数案例天善智能ForXMLPATH定义FOR XML PATH 语句能够把查询的数据生成XML数据在PATH 模式中，列名或列别名被作为XPath表达式来处理。这些表达式指明了如何将值映射到XML。每个XPath表达式都是一个相对XPath，它提供了项类型（例如属性、元素和标量值）以及将相对于行元素而生成的节点的名称和层次结构。天善智能ForXMLPATH应用范围没有名称的列具有名称的列名称

5、被指定为通配符(*) 的列以某个XPath节点测试的名称命名的列以指定为data() 的路径命名的列默认情况下包含NULL 值的列天善智能ForXMLPATH应用案例天善智能ForXMLPATH应用案例天善智能Pivot可以使用PIVOT 和UNPIVOT 关系运算符将表值表达式更改为另一个表。PIVOT 通过将表达式某一列中的唯一值转换为输出中的多个列来旋转表值表达式，并在必要时对最终输出中所需的任何其余列值执行聚合。UNPIVOT 与PIVOT 执行相反的操作，将表值表达式的列转换为列值。PIVOT 提供的语法比一系列复杂的SELECT.CASE 语句中所指定的语法更简单和更具可读性天善智

6、能Pivot应用案例天善智能Merge语法Merge关键字是一个神奇的DML关键字。它在SQL 2008被引入，它能将INSERT,Update,Delete简 单的并为一句。MSDN对于Merge的解释非常的短小精悍:”根据与源表联接的结果，对目标表执行插入、更新或删除操作。例如，根据在另一 个表中找到的差异在一个表中插入、更新或删 除行，可以对两个表进行同步。”,通过这个描述，我们可以看出Merge是关于对于两个表之间的数据进行操作的。数据同步数据转换基于源表对目标表做Insert,Update,Delete操作天善智能Merge应用案例天善智能Merge应用案例OUTPUT输出天善智能M

7、erge应用案例条件添加天善智能动态SQLExec sql不符合语法规则，建议不使用Exec (sql) 符合语法规则，建议经常使用.EXEC SP_EXECUTESQL SQL,-相当于存储过程的函数体PARAME_VALUE,-相当于存储过程中的参数定义RETURNVALUE=RETURN_VALUE OUTPUT相当于在外面调用存储过程MSSQL为我们提供了两种动态执行SQL语句的命令，分别 是EXEC和sp_executesql;通常,sp_executesql则更具有 优势，它提供了输入输出接口，而EXEC没有。还有一个 最大的好处就是利用sp_executesql，能够重用执行计划

8、， 这就大大提供了执行性能(对于这个我在后面的例子中会 详加说明)，还可以编写更安全的代码。EXEC在某些情况 下会更灵活。除非您有令人信服的理由使用EXEC，否侧 尽量使用sp_executesql. 天善智能动态SQL执行结果赋值给变量天善智能ON条件数据库在通过连接两张或多张表来返回记录时，都会生成一张中间的临时表，然后再将这张临时表返回给用户。在使用left jion时，on和where条件的区别如下：on条件是在生成临时表时使用的条件，它不管on 中的条件是否为真，都会返回左边表中的记录。where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。天善智能ON条件应用案例天善智能Except语法比较两个查询的结果，返回非重复值。EXCEPT