CTE 学习脚本

create table product
 (
ID int identity(1,1) primary key,
ProductName varchar(20),
productType varchar(20)
)

 insert product values('羊羊鞋','L')
 insert product values('羊羊鞋','XL')
 insert product values('羊羊鞋','XXL')
 insert product values('羊羊鞋','XXXL')
 insert product values('XX衬衫','L')
 insert product values('XX衬衫','XL')
 insert product values('XX衬衫','XXL')
;
 with cte (ProductName,productType)  as
  (   select ProductName,min(productType) from product 
      group by ProductName 
      union all  
      select p.ProductName,convert(varchar(20),p.productType+','+c.productType) from product p 
      inner join cte c on c.ProductName=p.ProductName 
      where p.ProductName=c.ProductName and p.productType>c.productType )
	select ProductName,max(productType)productType from cte group by ProductName

 http://www.cnblogs.com/wenjl520/archive/2010/01/18/1650393.html

CREATE TABLE Employee_Tree
    (
      Employee_NM NVARCHAR(50) ,
      Employee_ID INT PRIMARY KEY ,
      ReportsTo INT
    )

INSERT  INTO Employee_Tree
VALUES  ( 'Richard', 1, NULL )
INSERT  INTO Employee_Tree
VALUES  ( 'Stephen', 2, 1 )
INSERT  INTO Employee_Tree
VALUES  ( 'Clemens', 3, 2 )
INSERT  INTO Employee_Tree
VALUES  ( 'Malek', 4, 2 )
INSERT  INTO Employee_Tree
VALUES  ( 'Goksin', 5, 4 )
INSERT  INTO Employee_Tree
VALUES  ( 'Kimberly', 6, 1 )
INSERT  INTO Employee_Tree
VALUES  ( 'Ramesh', 7, 5 )
;
--创建递归查询
WITH    SimpleRecursive ( Employee_NM, Employee_ID, ReportsTo )
          AS ( SELECT   Employee_NM ,
                        Employee_ID ,
                        ReportsTo
               FROM     Employee_Tree
               WHERE    Employee_ID = 2
               UNION ALL
               SELECT   p.Employee_NM ,
                        p.Employee_ID ,
                        p.ReportsTo
               FROM     Employee_Tree p
                        INNER JOIN SimpleRecursive A ON A.Employee_ID = p.ReportsTo
             )
    SELECT  sr.Employee_ID AS empid ,
            sr.Employee_NM AS Emp ,
            et.Employee_NM AS Boss
    FROM    SimpleRecursive sr
            INNER JOIN Employee_Tree et ON sr.ReportsTo = et.Employee_ID