sql語句遞歸
① 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語句遞歸找到一個目錄下的所有子目錄
有一個存儲目錄樹的表,定義目錄間的關系欄位有兩個:
如下:(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 怎麼遞歸查詢的方法：
1．創建測試表，createtabletest＿connect（idnumber，p＿idnumber）；
④ 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帶遞歸的語法 參考如下sqlserver 真他資料庫也有類似
IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
create table tb(id varchar(3) , pid varchar(3) , name varchar(10))
insert into tb values('001' , null , '廣東省')
insert into tb values('002' , '001' , '廣州市')
insert into tb values('003' , '001' , '深圳市')
insert into tb values('004' , '002' , '天河區')
insert into tb values('005' , '003' , '羅湖區')
insert into tb values('006' , '003' , '福田區')
insert into tb values('007' , '003' , '寶安區')
insert into tb values('008' , '007' , '西鄉鎮')
insert into tb values('009' , '007' , '龍華鎮')
insert into tb values('010' , '007' , '松崗鎮')
GO
SELECT * FROM tb AS t
DECLARE @ID VARCHAR(3)
--查詢ID = '001'的所有子節點
SET @ID = '007'
;WITH Temp AS (
SELECT id,pid,NAME FROM tb AS t WHERE t.id=@ID
UNION ALL
SELECT t2.id,t2.pid,t2.NAME FROM tb t2 INNER JOIN Temp tm on t2.pid=tm.ID
)
SELECT * FROM Temp AS t WHERE id!=@ID
⑥ SQL遞歸查詢知多少
1、基本概念
公用表表達式 (CTE) 可以認為是在單個 SELECT、INSERT、UPDATE、DELETE 或 CREATE VIEW 語句的執行范圍內定義的臨時結果集。公用表表達式可以包括對自身的引用，這種表達式稱為遞歸公用表表達式。
創建遞歸查詢。有關詳細信息，請參閱使用公用表表達式的遞歸查詢。
在不需要常規使用視圖時替換視圖，也就是說，不必將定義存儲在元數據中。
啟用按從標量嵌套 select 語句派生的列進行分組，或者按不確定性函數或有外部訪問的函數進行分組。
在同一語句中多次引用生成的表。
MSDN上對CTE的介紹
T-SQL查詢進階--詳解公用表表達式(CTE)
CTE 的基本語法結構如下：
WITH expression_name [ ( column_name [,...n] ) ]
AS
( CTE_query_definition )
--只有在查詢定義中為所有結果列都提供了不同的名稱時，列名稱列表才是可選的。
--運行 CTE 的語句為：
SELECT <column_list> FROM expression_name;
即三個部分：
公用表表達式的名字（在WITH關鍵字之後）
查詢的列名（可選）
緊跟AS之後的SELECT語句（如果AS之後有多個對公用表的查詢，則只有第一個查詢有效）
⑦ 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
希望對你有幫助吧。