【IT168 评论】好几天没写东西了，心里感觉空空的，今天特别看了一下SQL server2005 的CTE，觉得CTE的使用性真的很强，在实际应用中也很广泛，我就把代码共享出来了，希望能帮上需要帮助的朋友。
1.先创建一个简单的表product
create table product
(
序号 int identity(1,1) primary key,
ProductName varchar(20),
productType varchar(20)6 )
(
序号 int identity(1,1) primary key,
ProductName varchar(20),
productType varchar(20)6 )
2.简单地插入一些数据
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')
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')
3.下面是CTE
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
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
结果图：
完成了，希望能帮到你!
