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

转载于:https://www.cnblogs.com/dgjack/archive/2011/06/03/2072039.html