--如何查找指定父节点下的所有子节点的一个应用: 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 */