sql語句遞歸查詢

① sql 怎麼遞歸查詢的方法：

1．創建測試表，createtabletest＿connect（idnumber，p＿idnumber）；

② 如何實現資料庫SQL遞歸查詢在不同資料庫中例子源代碼

sql 遞歸查詢的方法：
方法一：T-SQL遞歸查詢
with Dep as
(
select Id,DeptCode,DeptName from Department where Id=1
union all
select d.Id,d.DeptCode,d.DeptName from Dep
inner join Department d on dep.Id = d.ParentDeptId
)
select * from Dep
方法二：PL/SQL遞歸查詢
select Id,DeptCode,DeptName
from Department
start with Id = 1
connect by prior Id = ParentDeptId;

③ SQL中遞歸查詢

CREATE PROCEDURE [dbo].[SetColGroup](@sColItemCode VARCHAR(50),@ReportID VARCHAR(50) )
AS
-- 獲取ItemCode對應的GroupCode,並更新至臨時表中
BEGIN

DECLARE @sSQL VARCHAR(1000)
DECLARE @colGroupCode VARCHAR(50)
DECLARE @SuperColItemCode VARCHAR(50)
DECLARE @colSuperGroupCode VARCHAR(50)

--獲取下一級別的數據（這里的SuperColItemCode實際上是下一級數據）
DECLARE curGroupCodeTmp CURSOR LOCAL FOR
SELECT c.ColGroupCode,b.SuperColItemCode,d.ColGroupCode AS ColSuperGroupCode
FROM dbo.ReportItemCross b
INNER JOIN dbo.ReportColItem c ON b.ColItemCode = c.ColItemCode
INNER JOIN dbo.ReportColItem d ON b.SuperColItemCode = d.ColItemCode
WHERE b.ColItemCode = @sColItemCode AND b.ReportID = @ReportID
OPEN curGroupCodeTmp
FETCH NEXT FROM curGroupCodeTmp
INTO @colGroupCode,@SuperColItemCode,@colSuperGroupCode
WHILE @@FETCH_STATUS = 0
BEGIN
--此處進行實際數據處理
IF(ISNULL(@colGroupCode,'')<>'' AND ISNULL(@colSuperGroupCode,'')<>'')
SET @sSQL = 'update ##tmp set ' + @colSuperGroupCode + ' = ''' + @SuperColItemCode
+ ''' where ' + @colGroupCode + ' = ''' + @sColItemCode + ''''
EXECUTE(@sSQL)
--根據條件判斷是否結束遞歸
IF(ISNULL(@SuperColItemCode,'') <> '')
EXEC SetColGroup @SuperColItemCode,@ReportID
FETCH NEXT FROM curGroupCodeTmp
INTO @colGroupCode,@SuperColItemCode,@colSuperGroupCode
END

CLOSE curGroupCodeTmp
DEALLOCATE curGroupCodeTmp

END

④ sql語句實現遞歸查詢所有節點,mysql和oracle都能用的

首先說一下Oracle的遞歸查詢，相信大部分人都知道很簡單。無非start with connect by 函數。下面是從pId向子節點遞歸查詢的例子，unId是資料庫表中的主鍵。

如果是從子節點遞歸到父節點查詢，就把start with 換成unid，prior左右對換

下面再講MySql 的遞歸查詢方式。MySql沒有Oracle的強大功能，雖然都是同一個公司的產品。所以只能靠自己寫。有很多方法，用sql去循環查詢，或者寫存儲過程，我這里只提供一種。就是新建一個function函數。

表結構不說了，無非就是 Id ，pId，其他列。下面是創建一個遞歸查詢子節點的函數

DROP FUNCTION IF EXISTS queryChildrenPowerInfo;

CREATE FUNCTION `queryChildrenPowerInfo` (powerId VARCHAR(2000))

RETURNS VARCHAR(2000)

BEGIN

DECLARE sTemp VARCHAR(2000);

DECLARE sTempChd VARCHAR(2000);

SET sTemp = '$';

SET sTempChd = cast(powerId as CHAR);

WHILE sTempChd is not NULL DO

SET sTemp = CONCAT(sTemp, ',', sTempChd);

SELECT group_concat(id) INTO sTempChd FROM t_discretionary_power where FIND_IN_SET(pId,sTempChd)>0;

END WHILE;

return sTemp;

調用的時候：select queryChildrenPowerInfo(""); 該語句會返回Id和父Id等於傳入參數powerId的一個字元串，中間有逗號隔開如圖

下面這句代碼的意思是，查詢出 t_discretionary_power 表中，t.id 等於上面查詢出的結果集的數據。FIND_IN_SET(A,B)是MYSQL的函數。意思是查找在B集合中有A的數據。相當於In

select t.* from t_discretionary_power t where FIND_IN_SET(t.id,queryChildrenPowerInfo(''))

⑤ SQL遞歸查詢知多少

sql 遞歸查詢的方法：
方法一：T-SQL遞歸查詢
with Dep as
(
select Id,DeptCode,DeptName from Department where Id=1
union all
select d.Id,d.DeptCode,d.DeptName from Dep
inner join Department d on dep.Id = d.ParentDeptId
)
select * from Dep
方法二：PL/SQL遞歸查詢
select Id,DeptCode,DeptName
from Department
start with Id = 1
connect by prior Id = ParentDeptId;

⑥ 如何用一段SQL語句遞歸找到一個目錄下的所有子目錄

有一個存儲目錄樹的表,定義目錄間的關系欄位有兩個:
如下:(ClassID表示該目錄的編號,ClassParentID表示它的父目錄的編號)

ClassID ClassParentID
1 1
2 1
3 2
4 3
5 3

表示的關系應該是

-1--(根目錄)
|
-2--(第二級目錄)
|
-3--(第三級目錄)
|
-4--(第四級目錄)
|
-5--(第四級目錄)

這樣的目錄關系.

那麼我通過知道一個目錄的ClassID,如何通過一條SQL語句找到所有該目錄的子目錄?

如:

"SELECT * FROM 表 WHERE ClassParentID = "+ClassID+"and ClassParentID <>"+ClassID

但只能找到第一層的子目錄,如何找到所有子目錄,請大家給一條SQL語句實現!!

(因為事先不知道目錄有多少層,當然一定是一條帶有while循環的SQL語句)

1、

create table Test2
(
ClassID int,
ClassParentID int,
)
go

create proc Test2_proc
@nClassParentID int -- 要查詢的父節點ID 號
as
begin
set nocount on
--
declare @nClassID int,
@nIndex int
-- create TempTable
create table #ClassIDTemp
(
nIndex int identity(1,1),
ClassID int,
ClassParentID int,
nFlag int default(0),
)
create index #ClassIDTemp_Index on #ClassIDTemp(nIndex)

-- insert First RecordSet

insert into #ClassIDTemp(ClassID,ClassParentID) select
ClassID,ClassParentID from Test2 where ClassParentID =
@nClassParentID order by ClassParentID
-- Circle
while(0=0)
begin

select top 1 @nIndex = nIndex, @nClassID = ClassID
from #ClassIDTemp where nFlag = 0 order by nIndex
asc
if @@rowcount = 0
break
else
begin

insert into #ClassIDTemp(ClassID,ClassParentID) select
ClassID,ClassParentID from Test2 where ClassParentID =
@nClassID order by ClassParentID
end
update #ClassIDTemp set nFlag = 1 where nIndex = @nIndex
end

-- Select Result
select ClassID,ClassParentID from #ClassIDTemp order by nIndex
if exists(select * from sysobjects where name = '#ClassIDTemp')
drop table #ClassIDTemp
end
go

2、

declare @Table table ( ClassID int primary key , ClassParentID int)
declare @Test table ( ClassID int primary key , ClassParentID int)

insert into @Table values ( 1, 0 ) -- 建議這里是 1，0 而不是 1， 1
insert into @Table values ( 2, 1 )
insert into @Table values ( 3, 2 )
insert into @Table values ( 4, 3 )
insert into @Table values ( 5, 3 )

insert into @Test select * from @Table where ClassID = 2

while exists ( select * from @Table A
inner join @Test B
on A.ClassParentID = B.ClassID
where A.ClassID not in ( select ClassID from @Test ) )
begin
insert into @Test
select A.* from @Table A
inner join @Test B
on A.ClassParentID = B.ClassID
where A.ClassID not in ( select ClassID from @Test )
end

select * from @Test

3、

sql1 = "(SELECT ClassID FROM 表 WHERE ClassParentID =" + 變數 + ")"

sql2 = "(SELECT ClassID FROM 表 WHERE (ClassParentID IN
"+ sql1 + ") OR (ClassID IN " + sql1 + "))"

sql = "SELECT * FROM 表 WHERE ClassParentID IN " + sql2 + " OR ClassID IN " + sql2

這個辦法可實現1到4級的所有情況（包括只有2級、3級的情況）

4、--我的方法是用自定義函數實現,這樣具有通用性

--測試

--測試數據
create table 表(ClassID int,ClassParentID int)
insert 表 select 1,0
union all select 2,1
union all select 3,2
union all select 4,3
union all select 5,3
go

--自定義函數,返回指定目錄下的所有子目錄列表
create function f_child(@ClassID int)
returns @re table(ClassID int,Level int)
as
begin
declare @l int
set @l=0
insert @re select @ClassID,@l
while @@rowcount>0 --直接用@@rowcount判斷
begin
set @l=@l+1
insert @re select a.ClassID,@l
from 表 a join @re b on a.ClassParentID=b.ClassID
where b.level=@l-1
end
return
end
go

--調用函數實現查詢
select a.* from 表 a join dbo.f_child(1) b on a.ClassID=b.ClassID
go

--加上縮進效果
select ClassID=space(b.level*4)+cast(a.ClassID as varchar),a.ClassParentID
from 表 a join dbo.f_child(1) b on a.ClassID=b.ClassID

go
--刪除測試環境
drop table 表
drop function f_child

/*--測試結果
ClassID ClassParentID
----------- -------------
1 0
2 1
3 2
4 3
5 3

（所影響的行數為 5 行）

ClassID ClassParentID
-------------- ----------------
10
2 1
3 2
4 3
5 3

（所影響的行數為 5 行）
--*/

⑦ 如何用SQL遞歸查詢出下面的結果 SQL遞歸語法是怎麼查詢的

create table #A (a int,b int)
insert #a select 1 , 1000
insert #a select 2 , 2000
insert #a select 3 , 3000
insert #a select 4 , 4000

-------------------------------------------
create table #b(a int,b int)
insert into #bselect * from #a where a=1
declare @a int
declare @b int
select @a=min(a) from #a
select @b=max(a) from #A
while (@a<=@b)
begin

insert into #b
select t1.a,t1.b+t2.b
from #a t1,#b t2
where t1.a=t2.a+1
and t2.a=@a

set @a=@a+1
end
select * from #b

⑧ sql語句中怎麼實現遞歸查詢

在SQL
SERVER
2000
中你可以先一些自定義函數，或一些存儲過程，實現遞歸：
select
level,TypeName
from
ProctType
t
START
WITH
t.ParentID=0
CONNECT
BY
PRIOR
t.ProctTypeID=
t.ParentID;

⑨ 遞歸SQL語句

CREATETABLE#test(
Achar(1),
Bchar(1)
)
GO

INSERTINTO#testVALUES('a','b');
INSERTINTO#testVALUES('b','c');
INSERTINTO#testVALUES('c','d');
INSERTINTO#testVALUES('d','e');
INSERTINTO#testVALUES('e','f');
INSERTINTO#testVALUES('a','g');
INSERTINTO#testVALUES('a','h');
INSERTINTO#testVALUES('g','m');
INSERTINTO#testVALUES('m','n');
GO


WithmyCTEAS
(
SELECT
0ASLevel,A,B
FROM
#test
WHERE
B='e'
UNIONALL
SELECT
myCTE.Level+1ASLevel,
t.A,t.B
FROM
#testtJOINmyCTEON(myCTE.A=t.B)
)
SELECTtop1
AAS[最高父節點]
FROM
myCTE
ORDERBY
LevelDESC
GO

最高父節點
-----
a

(1行受影響)

WithmyCTEAS
(
SELECT
0ASLevel,A,B
FROM
#test
WHERE
B='e'
UNIONALL
SELECT
myCTE.Level+1ASLevel,
t.A,t.B
FROM
#testtJOINmyCTEON(myCTE.B=t.A)
)
SELECTtop1
BAS[最下面子節點]
FROM
myCTE
ORDERBY
LevelDESC
GO


最下面子節點
------
f

(1行受影響)

SQL Server 2008 Express 版本下測試通過。

⑩ 如何實現SQL語句的遞歸查詢

1．創建測試表，createtabletest＿connect（idnumber，p＿idnumber）；