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