Ⅰ sql 函數遞歸
給你一個例子，跟這個差不多：
CREATE TABLE T(ID INT ,PID INT)
INSERT INTO T SELECT 1,0
UNION ALL SELECT 2,0
UNION ALL SELECT 3,1
UNION ALL SELECT 4,1
UNION ALL SELECT 5,2
UNION ALL SELECT 6,4
UNION ALL SELECT 7,3
UNION ALL SELECT 8,4
GO
--測試數據
CREATE FUNCTION F(@ID INT)
RETURNS VARCHAR(20)
AS
BEGIN
DECLARE @R VARCHAR(20)
SET @R=@ID
WHILE ISNULL(@ID,'')<>''
BEGIN
SELECT @R=CAST(PID AS VARCHAR(20))+'.'+@R FROM T WHERE ID=@ID
SELECT @ID=PID FROM T WHERE ID=@ID
END
RETURN @R
END
GO
--生成目錄
CREATE FUNCTION F2(@F VARCHAR(20))
RETURNS VARCHAR(20)
BEGIN
DECLARE @R VARCHAR(20)
SET @R=''
SET @F=SUBSTRING(@F,4,LEN(@F))
WHILE CHARINDEX('.',@F,1)<>0
BEGIN
SET @F=SUBSTRING(@F,CHARINDEX('.',@F,1)+1,LEN(@F))
SET @R='-'+@R
END
RETURN @R
END
GO
--生成前綴符號
SELECT DBO.F2(DBO.F(ID))+CAST(ID AS VARCHAR) FROM T ORDER BY DBO.F(ID)
GO
--結果
DROP TABLE T
DROP FUNCTION F
DROP FUNCTION F2
----------------------------------------------------以下為結果集--------------------------------------------------------------
1
-3
--7
-4
--6
--8
2
-5
Ⅱ 如何實現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語句
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語句中怎麼實現遞歸查詢
在SQL
SERVER
2000
中你可以先一些自定義函數，或一些存儲過程，實現遞歸：
select
level,TypeName
from
ProctType
t
START
WITH
t.ParentID=0
CONNECT
BY
PRIOR
t.ProctTypeID=
t.ParentID;
Ⅵ sql 怎麼遞歸查詢的方法：
1．創建測試表，createtabletest＿connect（idnumber，p＿idnumber）；
Ⅶ SQL函數怎麼實現遞歸
declare @b nvarchar(2) =1;
with abc(id,b_PriorID,level)as
(
select id,b_PriorID,0 level from #tt where b_PriorID =@b
union all
select a.id,a.b_PriorID,t.level+1 from #tt a
inner join abc t
on t.id=a.b_PriorID
)
select * from abc
你試試可以么？
Ⅷ SQL遞歸查詢所有子節點
你這樣設計的表用遞歸來顯示最恰當 我剛剛好有這個的代碼 發給你參考下吧
/// <summary>
/// 綁定根節點
/// </summary>
/// <param name="id"></param>
/// <param name="ddlList"></param>
public void BindSysMenu(string id, DropDownList ddlList)
{
ListItem ll = new ListItem();
ll.Text = "╋--請選擇-----";
ll.Value = "-1";
ddlList.Items.Add(ll);
DataTable dt = new ManageContentInfoBll().GetWName(id).Tables[0];
foreach (DataRow dr in dt.Rows)
{
ListItem li = new ListItem();
li.Text = "╋" + dr["W_Name"].ToString();
li.Value = dr["W_ID"].ToString();
ddlList.Items.Add(li);
BindSysMenuChild((dr["W_ID"].ToString()), ddlList, "├—");
}
}
/// <summary>
/// 綁定子節點
/// </summary>
public void BindSysMenuChild(string id, DropDownList ddlList, String separtor)
{
DataTable dt = new ManageContentInfoBll().GetWName(id).Tables[0];
foreach (DataRow dr in dt.Rows)
{
ListItem li = new ListItem();
li.Text = separtor + dr["W_Name"].ToString();
li.Value = dr["W_ID"].ToString();
string separtor_ = separtor + "——";
ddlList.Items.Add(li);
BindSysMenuChild(dr["W_ID"].ToString(), ddlList, separtor_);
}
}
sql 語句 很簡單的
select * from WebSubjectMenu where W_Logo ="+id+"
W_Logo 是上級的ID
希望對你有幫助吧。
Ⅸ 如何實現資料庫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語句實現遞歸查詢所有節點,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(''))