在MySql下，怎么用SQL语句遍历一个树结构

f exists (select * from dbo.sysobjects where id = object_id(N'[tb]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [tb]

GO

--示例数据

create table [tb]([id] int identity(1,1),[pid] int,name varchar(20))

insert [tb] select 0,'中国'

union all select 0,'美国'

union all select 0,'加拿大'

union all select 1,'北京'

union all select 1,'上海'

union all select 1,'江苏'

union all select 6,'苏州'

union all select 7,'常熟'

union all select 6,'南京'

union all select 6,'无锡'

union all select 2,'纽约'

union all select 2,'旧金山'

go

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_id]') and xtype in (N'FN', N'IF', N'TF'))

drop function [dbo].[f_id]

GO

/*--树形数据处理

级别及排序字段

--邹建 2003-12(引用请保留此信息)--*/

/*--调用示例

--调用函数实现分级显示

select replicate('-',b.[level]*4)+a.name

from [tb] a,f_id()b

where a.[id]=b.[id]

order by b.sid

--*/

create function f_id()

returns @re table([id] int,[level] int,sid varchar(8000))

as

begin

declare @l int

set @l=0

insert @re select [id],@l,right(10000+[id],4)

from [tb] where [pid]=0

while @@rowcount>0

begin

set @l=@l+1

insert @re select a.[id],@l,b.sid+right(10000+a.[id],4)

from [tb] a,@re b

where a.[pid]=b.[id] and b.[level]=@l-1

end

return

end

go

--调用函数实现分级显示

select replicate('-',b.[level]*4)+a.name

from [tb] a,f_id()b

where a.[id]=b.[id]

order by b.sid

go

--删除测试

drop table [tb]

drop function f_id

go

/*--结果

中国

----北京

----上海

----江苏

--------苏州

------------常熟

--------南京

--------无锡

美国

----纽约

----旧金山

加拿大

--*/

树形结构统一使用下面的测试表与测试数据

CREATE TABLE test_tree (

test_id INT,

pid INT,

test_val VARCHAR(10),

PRIMARY KEY (test_id)

)

INSERT INTO test_tree VALUES(1, NULL, '.NET')

INSERT INTO test_tree VALUES(2, 1, 'C#')

INSERT INTO test_tree VALUES(3, 1, 'J#')

INSERT INTO test_tree VALUES(4, 1, 'ASP.NET')

INSERT INTO test_tree VALUES(5, 1, 'VB.NET')

INSERT INTO test_tree VALUES(6, NULL, 'J2EE')

INSERT INTO test_tree VALUES(7, 6, 'EJB')

INSERT INTO test_tree VALUES(8, 6, 'Servlet')

INSERT INTO test_tree VALUES(9, 6, 'JSP')

INSERT INTO test_tree VALUES(10, NULL, 'Database')

INSERT INTO test_tree VALUES(11, 10,'DB2')

INSERT INTO test_tree VALUES(12, 10,'MySQL')

INSERT INTO test_tree VALUES(13, 10,'Oracle')

INSERT INTO test_tree VALUES(14, 10,'SQL Server')

INSERT INTO test_tree VALUES(15, 13,'PL/SQL')

INSERT INTO test_tree VALUES(16, 15,'Function')

INSERT INTO test_tree VALUES(17, 15,'Procedure')

INSERT INTO test_tree VALUES(18, 15,'Package')

INSERT INTO test_tree VALUES(19, 15,'Cursor')

INSERT INTO test_tree VALUES(20, 14,'T-SQL')

Oracle

使用 START WITH CONNECT BY

语句实现树状查询

SQL>ed

Wrote file afiedt.buf

1 SELECT

2LPAD(' ', 2*(LEVEL-1)) || test_val AS test_val

3 FROM

4test_tree

5 START WITH

6test_id IN (1, 6, 10)

7* CONNECT BY PRIOR test_id = pid

SQL>/

TEST_VAL

-----------------------------------------------------------

.NET

C#

J#

ASP.NET

VB.NET

J2EE

EJB

Servlet

JSP

Database

DB2

TEST_VAL

-----------------------------------------------------------

MySQL

Oracle

PL/SQL

Function

Procedure

Package

Cursor

SQL Server

T-SQL

20 rows selected.

SQL Server

使用 Common Table Expression (CTE) 来实现 递归调用。

1>WITH StepCTE

2>AS

3>(

4>SELECT

5> test_id,

6> pid,

7> test_val,

8> 1 as Lev

9>FROM

10> test_tree

11>WHERE

12> test_id IN (1,6,10)

13>UNION ALL

14>SELECT

15> T.test_id,

16> T.pid,

17> T.test_val,

18> CTE.Lev + 1

19>FROM

20> test_tree T INNER JOIN StepCTE CTE

21> ON T.pid = CTE.test_id

22>)

23>SELECT

24> test_id, pid, test_val, Lev

25>FROM StepCTE

26>go

test_id pid test_val Lev

----------- ----------- ---------- -----------

1NULL .NET 1

6NULL J2EE 1

10NULL Database 1

11 10 DB2 2

12 10 MySQL2

13 10 Oracle 2

14 10 SQL Server 2

20 14 T-SQL3

15 13 PL/SQL 3

16 15 Function 4

17 15 Procedure4

18 15 Package 4

19 15 Cursor 4

7 6 EJB 2

8 6 Servlet 2

9 6 JSP 2

2 1 C# 2

3 1 J# 2

4 1 ASP.NET 2

5 1 VB.NET 2

(20 行受影响)

1.先找到 存储 外键约束或者所有约束信息的表或者数据字典 假设为user_re表

2.然后再DESC 查看他有那些字段

3.再找里面相应的字段

a.约束所在表(假设字段为fk_table)

b.外键的话应该有

c.表的字段(fk_col)参照表(ref_table)和参照表的字段(ref_table.col)

这些信息肯定都有了

然后按你的要求就是

select ref_table from user_re

where fk_table = 'T1' and fk_col = 'AAA' and ref_table.col = 'AAA'

那么得到的ref_table列就是你所要找的表了！~

你自己要做的就是去查找相应的数据字典，而且可能信息存在几个表中，那么可能还设计到表的连接。

---