A question about recursive query - 未名空间MITBBS历史存档

国际科技财经博客移民网络热点娱乐民生时事公众号

Redian新闻

>未名空间

>Database - 数据库

A question about recursive query

A question about recursive query# Database - 数据库

l*e2004-06-17 07:06

1 楼

I have a table like
id parent_id lastname firstname
1 0 A 1
2 0 B 1
3 1 A 2
4 3 A 3
....
So you can see id 1 is parent of id 3, id 3 is parent of id 4
How to right a single SQL query, when you give id = 1, it will return its own
record along with id 3 and id 4 records as its child and grandchild ?
Also, how to write a SQL query, when you gice id = 4, it will return its own
re

b*e2004-06-17 07:06

2 楼

For your first question, and for the second onw it's pretty much same.
Sorry it's not simple. We have to wait for Yukon to write simple recursive
queries
(For microsoft sql server)
Create Table #tmpTABLE (ID int, parent_id int, lastName varchar(50), firstName
varchar(50) )
declare xTmp scroll cursor for SELECT * from tablename
open xTmp
declare @myid int
declare @pid int
declare @lName varchar(50)
declare @fName varchar(50)
declare @found bit
declare @tmpID int
declare @tmpPID int
declare @num i

【在 l******e 的大作中提到】

: I have a table like
: id parent_id lastname firstname
: 1 0 A 1
: 2 0 B 1
: 3 1 A 2
: 4 3 A 3
: ....
: So you can see id 1 is parent of id 3, id 3 is parent of id 4
: How to right a single SQL query, when you give id = 1, it will return its own
: record along with id 3 and id 4 records as its child and grandchild ?

n*f2004-06-17 07:06

3 楼

Q: How to right a single SQL query, when you give id = 1, it will return its
own record along with id 3 and id 4 records as its child and grandchild ?
SELECT *
FROM Your_Table_Name
CONNECT BY PRIOR ID = parent_id
START WITH ID = 1
Q. Also, how to write a SQL query, when you gice id = 4, it will return its
own
record along with id 3 and id 1 records as its parent and grandparent ?
SELECT *
FROM Your_Table_Name
CONNECT BY PRIOR parent_id = id
START WITH ID = 4
这是典型的 recursive q