sql语句中如何实现递归查询?

Question

我有两张表：   
  产品表products：   
  productsID     //产品编号
  productname     //产品名称   
  ProductTypeID //所属类型编号  
    
  产品类型表ProductType:   
  ProductTypeID    //类型编号  
  TypeName         //类型名称   
  ParentID         //上级类型编号   在ProductType表中通过ProductTypeID    （上级产品编码）实现类型的所属关系和层次划分，分类型根类标识是“0”   
  假设有产品类型的层次如下：   
    
  产品类型a   
            产品类型a1   
                      产品类型a11   
                      产品类型a12   
                      产品类型a13   
            产品类型a2   
                      产品类型a21   
                              产品类型a211   
                      产品类型a22   
                      产品类型a23   
  
   
我现在想查询产品类型a 中的所有产品如何在一个sql语句中查出来？ 下面这样写报错：消息 156，级别 15，状态 1，第 1 行
关键字 'by' 附近有语法错误。
消息 319，级别 15，状态 1，第 1 行
关键字 'with' 附近有语法错误。如果此语句是公用表表达式、xmlnamespaces 子句或者更改跟踪上下文子句，那么前一个语句必须以分号结尾。
select P.ID,P.ParentID from dbo.B_ProductType P START WITH P.ParentID='0'
CONNECT BY PRIOR P.ID= P.ParentID;

Answer 1

/*----------------------------------------------
标题：SQL2005 BOM递归方法整理
（以公司组织架构为例）整理人：htl258(Tony)日期:2009.04.25(引用请保留此信息)
------------------------------------------------*/
-->创建测试环境
DECLARE @t TABLE
(
FullDept VARCHAR(20), --部门全称
Dept VARCHAR(20), --部门
ParentDept VARCHAR(20), --上级部门
Supervisor VARCHAR(20) --部门主管
)
INSERT @t SELECT 'S-IT' ,'IT','S' ,'Peter'
UNION ALL SELECT 'S-IT-CN' ,'CN','S-IT' ,'Mary' 
UNION ALL SELECT 'S-IT-CN-SH' ,'SH','S-IT-CN' ,'Jack' 
UNION ALL SELECT 'S-FS-AP' ,'AP','S-FS' ,'Colin' 
UNION ALL SELECT 'S-FS' ,'FS','S' ,'Jerry' 
UNION ALL SELECT 'S' ,'0' ,'0' ,'CiCi' 
/*
SELECT * FROM @t
--------------------
FullDept(部门全称) Dept(部门) ParentDept(上级部门) Supervisor(部门主管)
-------------------- -------------------- -------------------- --------------------
S-IT IT S Peter
S-IT-CN CN S-IT Mary
S-IT-CN-SH SH S-IT-CN Jack
S-FS-AP AP S-FS Colin
S-FS FS S Jerry
S 0 0 CiCi
(6 行受影响)
*/
-->1.使用CTE递归返回指定根的子树查询：
--->例1.查询部门主管为"Peter"的所有下级部门清单
;WITH t AS 
(
SELECT *,0 as lvl /*展开层次*/ FROM @t WHERE Supervisor = 'Peter'
UNION ALL 
SELECT a.*,b.lvl+1 FROM @t a /*原表*/ JOIN t b /*CTE*/ ON a.ParentDept = b.FullDept /*原表父项等于CTE子项*/
)
SELECT * FROM t ORDER BY FullDept;
/*
FullDept Dept ParentDept Supervisor lvl
-------------------- -------------------- -------------------- -------------------- -----------
S-IT IT S Peter 0
S-IT-CN CN S-IT Mary 1
S-IT-CN-SH SH S-IT-CN Jack 2(3 行受影响)
*/
--->例2.查询部门主管为"Cici"的所有下级部门清单
;WITH t AS 
(
SELECT *,0 as lvl /*展开层次*/ FROM @t WHERE Supervisor ='Cici'
UNION ALL 
SELECT a.*,b.lvl+1 FROM @t a /*原表*/ JOIN t b /*CTE*/ ON a.ParentDept = b.FullDept /*原表父项等于CTE子项*/
)
SELECT * FROM t ORDER BY FullDept;
/*
FullDept Dept ParentDept Supervisor lvl
-------------------- -------------------- -------------------- -------------------- -----------
S 0 0 CiCi 0
S-FS FS S Jerry 1
S-FS-AP AP S-FS Colin 2
S-IT IT S Peter 1
S-IT-CN CN S-IT Mary 2
S-IT-CN-SH SH S-IT-CN Jack 3(6 行受影响)
*/
-->2.使用CTE递归返回指定子项的父项查询：
--->例1.查询部门主管为"Jack"的所有上级部门清单
;WITH t AS 
(
SELECT *,0 as lvl /*展开层次*/ FROM @t WHERE Supervisor = 'Jack'
UNION ALL 
SELECT a.*,b.lvl+1 FROM @t a /*原表*/ JOIN t b /*CTE*/ ON b.ParentDept = a.FullDept /*CTE父项等于原表子项*/
)
SELECT * FROM t ORDER BY FullDept;
/*
FullDept Dept ParentDept Supervisor lvl
-------------------- -------------------- -------------------- -------------------- -----------
S 0 0 CiCi 3
S-IT IT S Peter 2
S-IT-CN CN S-IT Mary 1
S-IT-CN-SH SH S-IT-CN Jack 0(4 行受影响)
*/
--->例2.查询部门主管为"Colin"的所有上级部门清单
;WITH t AS 
(
SELECT *,0 as lvl /*展开层次*/ FROM @t WHERE Supervisor = 'Colin'
UNION ALL 
SELECT a.*,b.lvl+1 FROM @t a /*原表*/ JOIN t b /*CTE*/ ON b.ParentDept = a.FullDept /*CTE父项等于原表子项*/
)
SELECT * FROM t ORDER BY FullDept;
/*
FullDept Dept ParentDept Supervisor lvl
-------------------- -------------------- -------------------- -------------------- -----------
S 0 0 CiCi 2
S-FS FS S Jerry 1
S-FS-AP AP S-FS Colin 0(3 行受影响)
*/本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/wufeng4552/archive/2010/01/15/5193800.aspx

Answer 2

connect by 层级查询时 oracle 特有的 sql 语法，不能用于 sql server 中。
;with t as (
select ProductTypeID,TypeName,ParentID from ProductType where TypeName='a'
union all
select p.ProductTypeID,p.TypeName,p.ParentID from ProductType p, t where t.ParentID=p.ProductTypeID
)
select p.productsID,p.productName,t.TypeName from t, products p where t.ProductTypeID=p.ProductTypeID;

Answer 3

你参考这个吧：
Oracle中connect by...start with...的使用一、语法 
大致写法：select * from some_table [where 条件1] connect by [条件2] start with [条件3]; 
其中 connect by 与 start with 语句摆放的先后顺序不影响查询的结果，[where 条件1]可以不需要。 
[where 条件1]、[条件2]、[条件3]各自作用的范围都不相同： [where 条件1]是在根据“connect by [条件2] start with [条件3]”选择出来的记录中进行过滤，是针对单条记录的过滤， 不会考虑树的结构； [条件2]指定构造树的条件，以及对树分支的过滤条件，在这里执行的过滤会把符合条件的记录及其下的所有子节点都过滤掉； [条件3]限定作为搜索起始点的条件，如果是自上而下的搜索则是限定作为根节点的条件，如果是自下而上的搜索则是限定作为叶子节点的条件； 示例： 
假如有如下结构的表：some_table(id,p_id,name)，其中p_id保存父记录的id。 
select * from some_table t where t.id!=123 connect by prior t.p_id=t.id and t.p_id!=321 start with t.p_id=33 or t.p_id=66; 对prior的说明： 
    prior存在于[条件2]中，可以不要，不要的时候只能查找到符合“start with [条件3]”的记录，不会在寻找这些记录的子节点。要的时候有两种写法：connect by prior t.p_id=t.id 或 connect by t.p_id=prior t.id，前一种写法表示采用自上而下的搜索方式（先找父节点然后找子节点），后一种写法表示采用自下而上的搜索方式（先找叶子节点然后找父节点）。 二、执行原理 
connect by...start with...的执行原理可以用以下一段程序的执行以及对存储过程RECURSE()的调用来说明： /* 遍历表中的每条记录，对比是否满足start with后的条件，如果不满足则继续下一条， 
如果满足则以该记录为根节点，然后调用RECURSE()递归寻找该节点下的子节点， 
如此循环直到遍历完整个表的所有记录 。*/ 
for rec in (select * from some_table) loop 
if FULLFILLS_START_WITH_CONDITION(rec) then 
    RECURSE(rec, rec.child); 
end if; 
end loop; /* 寻找子节点的存储过程*/ 
procedure RECURSE (rec in MATCHES_SELECT_STMT, new_parent IN field_type) is 
begin 
APPEND_RESULT_LIST(rec); /*把记录加入结果集合中*/ 
/*再次遍历表中的所有记录，对比是否满足connect by后的条件，如果不满足则继续下一条， 
如果满足则再以该记录为根节点，然后调用RECURSE()继续递归寻找该节点下的子节点， 
如此循环直到找至叶子节点。*/ 
for rec_recurse in (select * from some_table) loop 
    if FULLFILLS_CONNECT_BY_CONDITION(rec_recurse.child, new_parent) then 
      RECURSE(rec_recurse,rec_recurse.child); 
    end if; 
end loop; 
end procedure RECURSE; 三、使用探讨 
    从上面的执行原理可以看到“connect by...start with...”构造树的方式是：（1）如果是自上而下方式，则把表中的每一条记录都作为根节点来生成树，所以表中有多少条记录就会构造出多少棵树。（2）如果是自下而上的搜索方式，则把表中的每一条记录都作为叶子节点来生成分支，所以表中有多少条记录就会生成多少条分支。 
    因此如果表中的记录不是严格遵照每条记录都只能有一个父记录的原则，那么就可能有部分记录会存在于多棵树中，那么在查找记录的时候就可能会出现找到多条重复记录的异常情况。