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;