l******e
发帖数: 956 | 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**e
发帖数: 2 | 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****f
发帖数: 905 | 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 |
|
