如果是在SQL 2000中，我们需要读取分层结构数据时，不得不借助递归。在SQL server 2005中，我们可以使用CTE，当然，好的数据结构设计可以避免你使用CTE，请看这里：

如果你是一个数据库设计新手，那么在sql server 2008中可以使用新的原生分层结构数据hierarchyid。

关于它的详细说明，请看MSDN:

需要注意的是，它可以改造传统数据表而成。但不可以实现与XML互相转换。

下面我们看一个示例：

if

OBJECT_ID

(

'

[Category_hierarchyid]

'

)

is

not

null

drop

table

[

Category_hierarchyid

]

go

--

创建表

CREATE

TABLE

[

Category_hierarchyid

]

(

H_ID hierarchyid

NOT

NULL

,

C_ID

INT

primary

key

identity

(

1000

,

1

)

NOT

NULL

,

C_Name

NVARCHAR

(

50

)

NOT

NULL

,

Title

NVARCHAR

(

50

)

NOT

NULL

)

GO

TRUNCATE

table

[

Category_hierarchyid

]

GO

INSERT

INTO

[

Category_hierarchyid

]

(

[

H_ID

]

,

[

C_Name

]

,

[

Title

]

)

select

'

/

'

,

'

蔬菜

'

,

'

蔬菜

'

union

all

select

'

/1/

'

,

'

根菜类

'

,

'

蔬菜

'

union

all

select

'

/2/

'

,

'

叶菜类

'

,

'

蔬菜

'

union

all

select

'

/3/

'

,

'

茄果类

'

,

'

蔬菜

'

union

all

select

'

/4/

'

,

'

甘蓝类

'

,

'

蔬菜

'

union

all

select

'

/1/1/

'

,

'

萝卜

'

,

'

蔬菜

'

union

all

select

'

/1/1/

'

,

'

胡萝卜

'

,

'

蔬菜

'

union

all

select

'

/4/2/

'

,

'

甘蓝

'

,

'

蔬菜

'

union

all

select

'

/2/4/

'

,

'

花椰菜

'

,

'

蔬菜

'

union

all

select

'

/3/3/

'

,

'

茄子

'

,

'

蔬菜

'

union

all

select

'

/3/3/

'

,

'

番茄

'

,

'

蔬菜

'

union

ALL

select

'

/1/1/5/

'

,

'

白萝卜

'

,

'

蔬菜

'

go

/*

**********SQL server 2008 中的原生分层数据：hierarchyid　3w@live.cn****************************

*/

--

查看所有的分类

SELECT

*

FROM

[

Category_hierarchyid

]

/*

H_ID C_ID C_Name Title

0x 1000 蔬菜 蔬菜

0x58 1001 根菜类 蔬菜

0x68 1002 叶菜类 蔬菜

0x78 1003 茄果类 蔬菜

0x84 1004 甘蓝类 蔬菜

0x5AC0 1005 萝卜 蔬菜

0x5AC0 1006 胡萝卜 蔬菜

0x85A0 1007 甘蓝 蔬菜

0x6C20 1008 花椰菜 蔬菜

0x7BC0 1009 茄子 蔬菜

0x7BC0 1010 番茄 蔬菜

0x5AE3 1011 白萝卜 蔬菜

*/

--

查看所有的分类及级别

SELECT

*

,H_ID.GetLevel()

AS

Level

FROM

[

Category_hierarchyid

]

/*

H_ID C_ID C_Name Title Level

0x 1000 蔬菜 蔬菜 0

0x58 1001 根菜类 蔬菜 1

0x68 1002 叶菜类 蔬菜 1

0x78 1003 茄果类 蔬菜 1

0x84 1004 甘蓝类 蔬菜 1

0x5AC0 1005 萝卜 蔬菜 2

0x5AC0 1006 胡萝卜 蔬菜 2

0x85A0 1007 甘蓝 蔬菜 2

0x6C20 1008 花椰菜 蔬菜 2

0x7BC0 1009 茄子 蔬菜 2

0x7BC0 1010 番茄 蔬菜 2

0x5AE3 1011 白萝卜 蔬菜 3

*/

--

查看根菜类及其子分类

DECLARE

@Parent

hierarchyid

SELECT

@Parent

=

H_ID

FROM

[

Category_hierarchyid

]

WHERE

C_ID

=

1001

--

--PRINT @Parent.ToString()

SELECT

*

,H_ID.GetLevel()

AS

Level

FROM

[

Category_hierarchyid

]

WHERE

H_ID.IsDescendantOf(

@Parent

)

=

1

/*

H_ID C_ID C_Name Title Level

0x58 1001 根菜类 蔬菜 1

0x5AC0 1005 萝卜 蔬菜 2

0x5AC0 1006 胡萝卜 蔬菜 2

0x5AE3 1011 白萝卜 蔬菜 3

*/

--

查看胡萝卜及其所有父分类

DECLARE

@Son

hierarchyid

SELECT

@Son

=

H_ID

FROM

[

Category_hierarchyid

]

WHERE

C_ID

=

1006

SELECT

*

,H_ID.GetLevel()

AS

Level

FROM

[

Category_hierarchyid

]

WHERE

@Son

.IsDescendantOf(H_ID)

=

1

/*

H_ID C_ID C_Name Title Level

0x 1000 蔬菜 蔬菜 0

0x58 1001 根菜类 蔬菜 1

0x5AC0 1005 萝卜 蔬菜 2

0x5AC0 1006 胡萝卜 蔬菜 2

*/

相关资源：

１、

2、

3、