在做数据库设计这块，很多时候表的数据模型就是典型的二叉树结构。

于是在查询数据的时候，就涉及到了数据的递归查询。

递归查询分为两种：1.从根节点查询自身以及所有的子节点；2.从子节点查询自身以及所有的父节点。

下面分别以Oracle 11g 数据库和MsSql 2008 数据库为例，来实现上述两种类型的递归查询

先建一张表

表名： TAdministrative (行政信息表)

字段名称

说明

AdministrativeID

行政编号

AdministrativePID

当前行政级别的上一行政级别编号
AdministrativeName
当前行政级别名称
AdministrativeRemark
当前行政级别备注
下面分别是Oracle和MsSql创建表的Sql语句：
Oracle：
drop table TAdministrative cascade constraints;
create table TAdministrative
(
AdministrativeID
VARCHAR2(38)  not
null,
AdministrativePID
VARCHAR2(38),
AdministrativeName
VARCHAR2(50),
AdministrativeRemark
VARCHAR2(50),
constraint
PK_TADMINISTRATIVE primary key (AdministrativeID)
);
comment on table TAdministrative is
'行政信息表';
comment on column TAdministrative.AdministrativeID is
'行政编号';
comment on column TAdministrative.AdministrativePID is
'当前行政级别的上一行政级别编号';
comment on column TAdministrative.AdministrativeName is
'当前行政级别名称';
comment on column TAdministrative.AdministrativeRemark
is
'当前行政级别备注';
MsSql：
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TAdministrative](
[AdministrativeID] [varchar](38) NOT NULL,
[AdministrativePID] [varchar](38) NULL,
[AdministrativeName] [varchar](50) NOT NULL,
[AdministrativeRemark] [varchar](50) NULL,
CONSTRAINT [PK_TAdministrative] PRIMARY KEY
CLUSTERED
(
[AdministrativeID] ASC
)WITH (PAD_INDEX  = OFF,
STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS
= ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description',
@value=N'行政级别表主键，行政编号' , @level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',@level1name=N'TAdministrative',
@level2type=N'COLUMN',@level2name=N'AdministrativeID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description',
@value=N'当前行政级别的上级行政级别' , @level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',@level1name=N'TAdministrative',
@level2type=N'COLUMN',@level2name=N'AdministrativePID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description',
@value=N'当前行政级别名称' , @level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',@level1name=N'TAdministrative',
@level2type=N'COLUMN',@level2name=N'AdministrativeName'
GO
创建完表后，向表中插入数据
INSERT INTO TAdministrative
(AdministrativeID,AdministrativePID,AdministrativeName,AdministrativeRemark)
VALUES
('5B2EF293B6B546948B0FEEDDF3C54FFD',null,'节点000','第一层');
INSERT INTO TAdministrative
(AdministrativeID,AdministrativePID,AdministrativeName,AdministrativeRemark)
VALUES
('DEB10D87887743B3A2611D617C1C5CF7','5B2EF293B6B546948B0FEEDDF3C54FFD','节点010','第二层');
INSERT INTO TAdministrative
(AdministrativeID,AdministrativePID,AdministrativeName,AdministrativeRemark)
VALUES
('EBADE19857AF4B3C8E8AF12AA42AD431','5B2EF293B6B546948B0FEEDDF3C54FFD','节点020','第二层');
INSERT INTO TAdministrative
(AdministrativeID,AdministrativePID,AdministrativeName,AdministrativeRemark)
VALUES
('9CA303A350F5443798300B9BCD4C279D','5B2EF293B6B546948B0FEEDDF3C54FFD','节点030','第二层');
INSERT INTO TAdministrative
(AdministrativeID,AdministrativePID,AdministrativeName,AdministrativeRemark)
VALUES
('4DBF77EBDA0941D98991555014EDDB9B','DEB10D87887743B3A2611D617C1C5CF7','节点011','第三层');
INSERT INTO TAdministrative
(AdministrativeID,AdministrativePID,AdministrativeName,AdministrativeRemark)
VALUES
('5A66EC48CA8A4758AF38664F3C6D5810','DEB10D87887743B3A2611D617C1C5CF7','节点012','第三层');
INSERT INTO TAdministrative
(AdministrativeID,AdministrativePID,AdministrativeName,AdministrativeRemark)
VALUES
('46A0D8F0DF7545008867687CFCC817A2','DEB10D87887743B3A2611D617C1C5CF7','节点013','第三层');
INSERT INTO TAdministrative
(AdministrativeID,AdministrativePID,AdministrativeName,AdministrativeRemark)
VALUES
('49139B130B2148D4985CB0BEC6C4C5E2','EBADE19857AF4B3C8E8AF12AA42AD431','节点021','第三层');
INSERT INTO TAdministrative
(AdministrativeID,AdministrativePID,AdministrativeName,AdministrativeRemark)
VALUES
('B68A4B9D967E483586D9DF2880084E9C','EBADE19857AF4B3C8E8AF12AA42AD431','节点022','第三层');
INSERT INTO TAdministrative
(AdministrativeID,AdministrativePID,AdministrativeName,AdministrativeRemark)
VALUES
('925235B0149A48F8A21CC325071FE61E','EBADE19857AF4B3C8E8AF12AA42AD431','节点023','第三层');
INSERT INTO TAdministrative
(AdministrativeID,AdministrativePID,AdministrativeName,AdministrativeRemark)
VALUES
('FFFBD3FC63EB428DA5D8F34EC20E9C9F','9CA303A350F5443798300B9BCD4C279D','节点031','第三层');
INSERT INTO TAdministrative
(AdministrativeID,AdministrativePID,AdministrativeName,AdministrativeRemark)
VALUES
('25D8C293B5A448E9B821558A0AFDB0A2','9CA303A350F5443798300B9BCD4C279D','节点032','第三层');
INSERT INTO TAdministrative
(AdministrativeID,AdministrativePID,AdministrativeName,AdministrativeRemark)
VALUES
('F5BC7A7C6F724B1DBF3835B06289C8BE','9CA303A350F5443798300B9BCD4C279D','节点033','第三层');
--------------------------------------------------------------------------------------------------
下面是查询的重点：
MsSql：
1.从根节点查询到子节点
----从根节点查向子节点
WITH
CET_Administrative(AdministrativeID,AdministrativePID,AdministrativeName,AdministrativeRemark)
AS
(
----固定数据
SELECT
AdministrativeID,AdministrativePID,AdministrativeName,AdministrativeRemark
FROM TAdministrative WHERE AdministrativePID IS NULL
UNION ALL
----递归数据
SELECT T.AdministrativeID,T.AdministrativePID,
T.AdministrativeName,T.AdministrativeRemark
FROM TAdministrative T
INNER JOIN CET_Administrative ON
CET_Administrative.AdministrativeID=T.AdministrativePID
)
SELECT
AdministrativeID,AdministrativePID,AdministrativeName,AdministrativeRemark
FROM CET_Administrative
OPTION (MAXRECURSION 0);
GO
结果如图：
然后是由子节点查询到根节点
WITH
CET_Administrative(AdministrativeID,AdministrativePID,AdministrativeName,AdministrativeRemark)
AS
(
----固定数据
SELECT
AdministrativeID,AdministrativePID,AdministrativeName,AdministrativeRemark
FROM TAdministrative WHERE AdministrativeID =
'25D8C293B5A448E9B821558A0AFDB0A2'
UNION ALL
----递归数据
SELECT T.AdministrativeID,T.AdministrativePID,
T.AdministrativeName,T.AdministrativeRemark
FROM TAdministrative T
INNER JOIN CET_Administrative ON
CET_Administrative.AdministrativePID=T.AdministrativeID
)
SELECT
AdministrativeID,AdministrativePID,AdministrativeName,AdministrativeRemark
FROM CET_Administrative
OPTION (MAXRECURSION 0);
GO
Oracle：
从根节点到子节点：
SELECT
AdministrativeID,AdministrativePID,AdministrativeName,AdministrativeRemark
FROM TAdministrative START WITH AdministrativePID IS
NULL
CONNECT BY PRIOR AdministrativeID=AdministrativePID;
从子节点查询到根节点：
SELECT
AdministrativeID,AdministrativePID,AdministrativeName,AdministrativeRemark
FROM TAdministrative START WITH AdministrativeID
='25D8C293B5A448E9B821558A0AFDB0A2'
CONNECT BY PRIOR AdministrativePID=AdministrativeID;
比较：
MsSql和Oracle中，从根节点查询到子节点得到的都是一样的，但是从根节点查询到子节点就不同了。
MsSql是：第一层；第二层，第二层，第二层；第三层，第三层，第三层，第三层，第三层，第三层···；
Oracle是：第一层，第二层，第三层，第三层，··；第二层，第三层，第三层，··；第二层，第三层，第三层，··；
两者从根节点到子节点的遍历方式不一样，Oracle是先序遍历，至于MsSql其实是迭代的一种实现。大家可以看看MSDN的CTE解释：
MsSql的语法如下：
WITH cte_name ( column_name [,...n] )
AS
(
CTE_query_definition –-
Anchor member is
defined.
UNION ALL
CTE_query_definition –-
Recursive member is defined referencing
cte_name.
)
-- Statement using the CTE
SELECT *
FROM cte_name

递归执行的语义如下：

将 CTE 表达式拆分为定位点成员和递归成员。

运行定位点成员，创建第一个调用或基准结果集 (T0)。

运行递归成员，将 Ti 作为输入，将

Ti+1 作为输出。

重复步骤 3，直到返回空集。

返回结果集。这是对 T0 到

Tn 执行 UNION ALL 的结果。

Anchor member is defined. 这个位置查询的是数据集T0，然后Recursive

member is defined referencing

cte_name这个位置开始讲T0的数据代入其中根据条件再做计算得到T1，依次代入直到得带Ti为空集结束(当然，不是无限制的递归，读者自己看msdn的解释)。

oracle语法如下：

SELECT ... FROM TABLENAME START

WITH COND1 CONNECT

BY COND2 WHERE

COND3;

START WITH

是起始条件，就是说第一条数据时从哪里开始的

CONNECT BY

是连接条件，即其实数据集和下一个数据集的联系，上面是 START WITH AdministrativeID

='25D8C293B5A448E9B821558A0AFDB0A2' CONNECT BY

PRIOR AdministrativePID=AdministrativeID;

即由子节点查询到根节点时，先得到起始子节点，然后连接条件 AdministrativePID=AdministrativeID

等号左边的是子节点的父节点编号，右边是父节编号，即找到“当前子节点的父节点编号=父节点编号”的集合。