MS SQL的CTE及MERGE用法

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;

《MS SQL的CTE及MERGE用法》有1个想法

  1. select top 20 ”+DocNo from order_info for xml path(”) — 查询出某个字段拼接结果
    select stuff(c1, n1, n2, c2) — 把字符串c1中的第n1开始的n2个字符串,用c2替换掉

评论已关闭。