SQL cte select 与 update 实例

在SQL2005下测试通过，SQL2000未知

CTE的语法：
[ WITH <common_table_expression> [ ,n ] ]
<common_table_expression>::=
        expression_name [ ( column_name [ ,n ] ) ]
    AS
        ( CTE_query_definition )

___________________________________________

create table #tb (id int, name nvarchar(200), up_id int)
go
insert #tb
select 1 ,N'1' ,0 union all
select 2 ,N'2', 1 union all
select 21 ,N'21', 2 union all
select 22 ,N'22', 2 union all
select 220 ,N'220', 22 union all
select 2220 ,N'2220' ,220 union all
select 25 ,N'25', 2 union all

select 210 ,N'210', 21

;with cte_c as (
    select id,name,up_id from #tb where id = 2
    union all
    select  t.id,t.name,t.up_id from #tb t
    inner join cte_c c
    on t.up_id = c.id
)
select * from cte_c

/*
2    2    1
21    21    2
22    22    2
25    25    2
220    220    22
2220    2220    220
210    210    21
*/

------------SELECT 实例-----------

WITH cte_c AS (SELECT     UserId, UserName, SuperiorID, UserLevel
                                  FROM         B_UserList
                                  WHERE     (UserId = 4)
                                  UNION ALL
                                  SELECT     t.UserId, t.UserName, t.SuperiorID, t.UserLevel
                                  FROM         B_UserList AS t INNER JOIN
                                                        cte_c AS c ON t.SuperiorID = c.UserId)   
SELECT     UserId, UserName, SuperiorID, UserLevel
     FROM         cte_c AS cte_c_1
     WHERE     (UserLevel = 5)

 

---------------------Select 扩展查询 cte 外嵌套查询---------------------------

WITH cte_c AS (SELECT     UserId, UserName, SuperiorID, UserLevel
                                  FROM         B_UserList
                                  WHERE     (UserId = 4)
                                  UNION ALL
                                  SELECT     t.UserId, t.UserName, t.SuperiorID, t.UserLevel
                                  FROM         B_UserList AS t INNER JOIN
                                                        cte_c AS c ON t.SuperiorID = c.UserId)
    SELECT     Ma, MaType
     FROM         C_RecordList
     WHERE     (D5ID IN
                                (SELECT     UserId
                                  FROM          cte_c AS cte_c_1
                                  WHERE      (UserLevel = 5)))

------------------Update 实例-----------------------

WITH cte_c AS   (SELECT     UserId, UserName, SuperiorID
                                  FROM         B_UserList
                                  WHERE     (UserId = 4)
                                  UNION ALL
                                  SELECT     t.UserId, t.UserName, t.SuperiorID
                                  FROM         B_UserList AS t INNER JOIN
                                                        cte_c AS c ON t.SuperiorID = c.userid)
update b_userlist
set regdatetime='2011-6-3' from cte_c where cte_c.userid=b_userlist.userid