递归的一些典型应用

--如何查找指定父节点下的所有子节点的一个应用:
go
create table A
(单号 varchar(20) not null,
销售日期 datetime ,  
类型 varchar(10) null,
原单号 varchar(20) null
)
go
insert into A
select 'O201205080001','2012-05-08 11:13:52.750','销售',NULL union  
select 'I201205110002','2012-05-11 12:13:52.750','退货','O201205080001' union  
select 'O201205110002','2012-05-11 13:13:52.750','换货','I201205110002' union  
select 'O201205050001','2012-05-05 11:13:52.750','销售',NULL union  
select 'I201205110003','2012-05-11 12:22:52.750','退货','O201205050001' union  
select 'O201205110004','2012-05-11 13:22:52.750','换货','I201205110003' union  
select 'O201205130003','2012-05-13 11:13:52.750','销售',NULL

--要求查询出最开始的单号的销售日期和当前日期的时间差小于七天的
--关于2000的递归
go
create table #ttt
(
单号 varchar(20) not null
)
go
insert #ttt select 单号 from A where  DATEDIFF(DD,销售日期,GETDATE())>7
while @@rowcount<>0
begin
   insert #ttt select a.单号 from A a inner join #ttt b
   on a.原单号=b.单号  and 
   not exists(select 1 from #ttt where 单号=a.单号)
end

select 单号 from A where 单号 not in(select * from #ttt)

/*
单号
O201205080001
I201205110002
O201205110002
O201205130003
*/

go
create table A
(单号 varchar(20) not null,
销售日期 datetime ,  
类型 varchar(10) null,
原单号 varchar(20) null
)
go
insert into A
select 'O201205080001','2012-05-08 11:13:52.750','销售',NULL union  
select 'I201205110002','2012-05-11 12:13:52.750','退货','O201205080001' union  
select 'O201205110002','2012-05-11 13:13:52.750','换货','I201205110002' union  
select 'O201205050001','2012-05-05 11:13:52.750','销售',NULL union  
select 'I201205110003','2012-05-11 12:22:52.750','退货','O201205050001' union  
select 'O201205110004','2012-05-11 13:22:52.750','换货','I201205110003' union  
select 'O201205130003','2012-05-13 11:13:52.750','销售',NULL

--2000版本还可以创建处理函数,方法同上面的一样，

--2005版本以上使用with as公用表达式递归实现:

;WITH T
AS(
SELECT * FROM A WHERE 单号 IN(
SELECT DISTINCT 单号
FROM A WHERE DATEDIFF(DD,销售日期,GETDATE())>7
)
UNION ALL
SELECT A.* FROM A,T 
WHERE A.原单号=T.单号
)
SELECT 单号 FROM A
WHERE 单号 NOT IN(SELECT 单号 FROM T)
/*
单号
O201205080001
I201205110002
O201205110002
O201205130003
*/