CTE应用于 >=SQL Server 2005

MERGE应用于 >= SQL Server 2008

 

CTE: Common table expression

with CTE_NAME [(column_name [,…n] )] as

(

sql_query_script

)

select … from CTE_NAME

应用场景1：分页

with ttt as (

select row_number()  OVER(Order By ID) as n, * from table1

)

select * from ttt where n between 10001 and 10100

应用场景2：递归查询

WITH COL_CTE(Id,Name,ParentId,tLevel )
AS
(
    --基本语句
    SELECT Id,Name,ParentId,0 AS tLevel FROM Col
    WHERE ParentId = 0
    UNION ALL
    --递归语句
    SELECT c.Id,c.Name,c.ParentId,ce.tLevel+1 AS tLevel FROM COL as c 
    INNER JOIN COL_CTE AS ce 
    ON c.ParentId = ce.Id
)

SELECT * FROM COL_CTE
OPTION(MAXRECURSION 2)　　--指定最大递归次数为2

FOR XML PATH用法

select stuff((select top 2 ‘ ‘+DocNo from OrderInfo for xml path(”)),1,1,”)

查询出拼接成一个字符串的数据

用于GROUP更方便

select …… , stuff((select distinct ‘,’ + isnull(子表.字段2,”) from 子表 where 子表.字段1=主表.字段 for xml path(”)),1,1,”)

from 主表

MERGE用法：

把一个表或者查询的结果，合并到另一个表中，可以方便指定insert或者update的字段。

MERGE ProductNew AS d
USING
Product
AS s
ON s.ProductID = d.ProductId
WHEN NOT MATCHED THEN
INSERT( ProductID,ProductName,Price)
VALUES(s.ProductID,s.ProductName,s.Price)
WHEN MATCHED THEN
UPDATE SET d.ProductName = s.ProductName, d.Price = s.Price;

— 如果需要同步删除的数据，需要指定WHEN NOT MATCHED BY SOURCE，即按源数据匹配不到目的数据。如果同步删数据，则表示全量删除，否则会删除目的表中不应该删除的数据。

MERGE ProductNew AS d
USING
Product
AS s
ON s.ProductID = d.ProductId
WHEN NOT MATCHED BY TARGET THEN
INSERT( ProductID,ProductName,Price)
VALUES(s.ProductID,s.ProductName,s.Price)
WHEN NOT MATCHED BY SOURCE THEN
DELETE
WHEN MATCHED THEN
UPDATE SET d.ProductName = s.ProductName, d.Price = s.Price;