CET (树形结构递归查询)、两个表同步

分手后的思念是犯贱 2022-07-16 19:29 127阅读 0赞

\---转载

\-----------------------------------------------树形结构递归查询(CTE 即 公用表表达式)  
 --例如：  
CREATE TABLE MENU  (    name nvarchar(50) NOT NULL PRIMARY KEY,    senior nvarchar(50) NULL);  INSERT INTO MENU values    ('文件',NULL),    ('新建','文件'),    ('项目','新建'),    ('使用当前连接查询','新建');

WITH lmenu(name,senior,level) as (    SELECT NAME,SENIOR,0 level FROM MENU WHERE SENIOR IS NULL     
          UNION ALL     
          SELECT A.NAME,A.SENIOR,b.level+1 FROM MENU A,lmenu b    where a.senior = b.name)  
          SELECT \*  from lmenu  
\--解释说明

注意查询定义语句，它由两条查询语句构成，其中

SELECT NAME,SENIOR,0 level FROM MENU WHERE SENIOR IS NULL

称为定位成员，SQL Server通过此语句来判断是否继续进行递归。  
语句

SELECT A.NAME,A.SENIOR,b.level+1 FROM MENU A,lmenu b  
    where a.senior = b.name

称之为递归成员，其特征为from子句中引用了CTE对象自身。  
递归CTE具有一些限制条件(引自MSDN)：

至少有一个定位点成员和一个递归成员，当然，你可以定义多个定位点成员和递归成员，但所有定位点成员必须在递归成员的前面  
定位点成员之间必须使用UNION ALL、UNION、INTERSECT、EXCEPT集合运算符，最后一个定位点成员与递归成员之间必须使用UNION ALL，递归成员之间也必须使用UNION ALL连接  
定位点成员和递归成员中的字段数量和类型必须完全一致  
递归成员的FROM子句只能引用一次CTE对象  
递归成员中不允许出现下列项  
    SELECT DISTINCT  
    GROUP BY  
    HAVING  
    标量聚合  
    TOP  
    LEFT、RIGHT、OUTER JOIN（允许出现 INNER JOIN）  
    子查询

CTE递归查询的执行方式：  
递归的终止依赖于定位点成员的，如果理解了这一点，也就理解了递归查询的执行方式。  
我们来看上例的执行执行过程：

SELECT \* FROM lmenu

这条语句进入递归查询

SELECT A.NAME,A.SENIOR,b.level+1 FROM MENU A,lmenu b  
    where a.senior = b.name

作为最外层的语句，显然递归的第一层应该根据MENU表的记录来循环(如果查询执行计划，这表示一个嵌套循环)，假设menu表中查询出的记录顺序如下：

name                                               senior  
\-------------------------------------------------- --------------------------------------------------  
文件                                                 NULL  
新建                                                 文件  
使用当前连接查询                               新建  
项目                                                 新建

第一条记录：  
首先判断是否进入递归，由于 文件包含在定位点成员结果集中，不符合递归条件，所以不进入递归，直接返回从定位点成员集合中返回记录：

select name,senior,0 level from menu where senior is null and name='文件'

name                                               senior                                             level  
\-------------------------------------------------- -------------------------------------------------- -----------  
文件                                                 NULL                                               0

第二条记录：  
即NAME = '新建', 定位点成员结果集中没有该记录，将进入递归：  
将当前行的值带入递归成员：

SELECT A.NAME,A.SENIOR,b.level+1 level FROM MENU A,lmenu b  
    where a.senior = b.name  
        AND a.senior = '文件'  
        AND a.name='新建'

由于递归的关联条件是a.senior = b.name，所以b.name='文件'，以此条件进入下级递归,这实际上就是第一条记录的情况，由于name='文件'符合定位点条件，所以将终止递归，如果我们用子查询来替换掉lmenu递归成员，第二条记录的查询语句实际为：

SELECT a.name,a.senior,b.level+1 from menu a, (  select name,senior,0 level  from menu where senior is null and name='文件') bwhere a.senior=b.name    and a.senior = '文件'    and a.name='新建'

第三条记录：  
NAME='使用当前连接查询'，同样不符合定位点条件，将进入递归：

SELECT A.NAME,A.SENIOR,b.level+1 FROM MENU A,lmenu b  
    where a.senior = b.name  
          AND a.senior = '新建'  
          AND a.name = '使用当前连接查询'

同样，代入当前记录条件，下级递归b.name='新建',由于'新建'还不符合定位点条件，所以还将继续递归，及lmenu b表示子查询：

select c.name,c.senior,d.level+1 level from menu c,lmenu d  
where c.senior = d.name  
     and c.name = '新建'  
     and c.senior = '文件'

替换成上述语句后，d.name='文件'，将再次判断是否需要继续递归，由于'文件'符合终止递归条件，所以将终止递归。  
我们用子查询表示第三条记录的递归过程如下

SELECT a.name,a.senior,b.level+1 level FROM menu A,(        select c.name,c.senior,d.level+1 level from menu c,(          select name,senior,0 level from menu where senior is null and name='文件'    ) d    where c.senior = d.name        and c.name = '新建'        and c.senior = '文件'   ) bwhere a.senior = b.name    and a.senior = '新建'    and a.name = '使用当前连接查询'  
第四条记录与第三条记录的递归层次完全一样。

\-----------------------------------------------树形结构递归查询(CTE 即 公用表表达式)

\------两表数据同步  
\----------------------------------BEGIN

Merge 它的功能是根据源表对目标表执行插入、更新或删除操作。最典型的应用就是进行两个表的同步。

MERGE语句的基本语法：

MERGE 目标表

USING 源表

ON 匹配条件

WHEN MATCHED THEN

语句

WHEN NOT MATCHED THEN

语句;

语句执行时根据匹配条件的结果，如果在目标表中找到匹配记录则执行WHEN MATCHED THEN后面的语句，如果没有找到匹配记录则执行WHEN NOT MATCHED THEN后面的语句。注意源表可以是表，也可以是一个子查询语句。  
格外强调一点，MERGE语句最后的分号是不能省略的!

回到我们的示例，显然Product与ProductNew表的MERGE匹配条件为主键ProductID字段，初始情况下，ProductNew表为空，此时肯定执行的是WHEN NOT MATCHED THEN后的语句，我们先只考虑源表递增的情况，MERGE语句如下：

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);

运行后2行受影响，我们已经将Product表的数据同步到了ProductNew表。

现在，我们更新Product表4100037产品的价格，将其修改为55：

UPDATE Product SET Price=55 WHERE ProductID='4100037';

我们也希望每天同步的时候应该将更新后的价格同步到ProductNew表，显然此时在MERGE语句中应该添加WHEN MATCHED THEN 语句，该语句来更新ProductNew表的价格，添加匹配更新后的MERGE语句：

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;

执行后2行受影响，为什么是两行呢？因为我们的匹配条件只是按ProductID来关联的，这样匹配出来的记录为2行。另外，我们的UPDATE语句里面没有更新ProductID字段，因为这是完全没必要的(如果修改了ProductID字段会直接走到NOT MATCHED)。

现在做个破坏，我们将410037产品删除掉：

DELETE Product WHERE ProductID='4100037';

明显，上面给出的MERGE语句无法同步这种情况，再次回到MERGE语句的定义，对MERGE的WHEN NOT MATCHED THEN语句稍作扩展：

WHEN NOT MATCHED BY TARGET

表示目标表不匹配，BY TARGET是默认的，所以上面我们直接使用WHEN NOT MATCHED THEN

WHEN NOT MATCHED BY SOURCE

表示源表不匹配，即目标表中存在，源表中不存在的情况。

OK，现在我们要完成源表DELETE后，目标表的同步动作，MERGE语句如下：

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;

嗯，上面已经使用到MERGE语句中的INSERT、UPDATE、DELETE语句，这足够完成大多数的同步功能了。  
\-------------------------------------------------------------------------------------------------OVER

\--递归删除父节点及所有子节点  
create table tb\_test(Id int, ParentId int, Name varchar(5))  
insert  tb\_test select 1, 0, 'a1'  
union all select 2,2, 'a2'  
union all select 14, 1, 'b11'  
union all select 15, 1, 'b12'  
union all select 16, 14, 'c13'  
union all select 17, 14, 'c14'  
union all select 104,17,'d15'  
go  
WITH temptab(id, parentid, name) AS  
   (    SELECT root.id, root.parentid, root.name  
            FROM tb\_test root  
            WHERE id=1  
     UNION ALL  
        SELECT sub.id, sub.parentid, sub.name  
            FROM tb\_test sub, temptab super  
            WHERE sub.parentid = super.id  
   )  
delete from tb\_test where id in(  
select id from temptab  
)