A. sql通過父節點獲取所有子節點
這個很簡單啊
表結構一般如下tablename(表名)
id--節點ID, name-- 節點名稱 parentid父節點ID,
-----獲取節點號為6下的所有子節點
select * from tablename t start with id =6 connect by prior id=parentid
B. MSSQL如何檢索父節點下的所有子節點
Tanle_2 包含所有子節點的表 必須有 Table_1的外鍵 name這一列(設為Table_1_NAME )select * from Table_1,Tanle_2 where Table_1.ID = 3 and Table_1.NAME = Table_2.Table_1_NAME
C. 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(''))
D. sql語句：如何通過id查找所有的子節點
使用SQL遞歸查詢
Declare@IdInt
Set@Id=0;---在此修改父節點
WithRootNodeCTE(D_ID,D_FatherID,D_Name,lv)
As
(
SelectD_ID,D_FatherID,D_Name,0aslvFrom[LFBMP.LDS].[dbo].[LDS.Dictionary]WhereD_FatherIDIn(@Id)
UnionAll
Select[LFBMP.LDS].[dbo].[LDS.Dictionary].D_ID,[LFBMP.LDS].[dbo].[LDS.Dictionary].D_FatherID,[LFBMP.LDS].[dbo].[LDS.Dictionary].D_Name,lv+1FromRootNodeCTE
InnerJoin[LFBMP.LDS].[dbo].[LDS.Dictionary]
OnRootNodeCTE.D_ID=[LFBMP.LDS].[dbo].[LDS.Dictionary].D_FatherID
)
Select*FromRootNodeCTE
;WithTB([Cd_ID],[ConstituteID],[Cd_PID],[Cd_CName],lv)
as(
Select[Cd_ID],[ConstituteID],[Cd_PID],[Cd_CName],0aslvFROM[LFBMP.Center].[dbo].[ConstituteDetail]Where[Cd_PID]=0And[ConstituteID]=4
unionall
SelectA.[Cd_ID],A.[ConstituteID],A.[Cd_PID],A.[Cd_CName],lv+1FROMTBinnerjoin[LFBMP.Center].[dbo].[ConstituteDetail]asA
onTB.[Cd_ID]=A.Cd_PID
)
Select*FromTB
E. SQL語句查詢出一個父節點下的所有子節點
假如你的表名字是tt，創建下面的存儲過程，使用存儲過程即可查詢。
create proc querytree
@user varchar(100)
as
begin
declare @id int
declare @T_tmp table(id int,username varchar(100),parentid int)
insert into @T_tmp select * from tt where username=@user
while(@@rowcount>0)
begin
insert into @T_tmp select * from tt where parentid in (select id from @T_tmp) and id not in(select id from @T_tmp)
end
select * from @T_tmp where username<>@user
end
F. 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
希望對你有幫助吧。
G. 200分求助！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
H. sqlserver查詢樹形結構的所有子節點
sqlserver查詢樹形結構的所有子節點
用標准sql的with實現遞歸查詢（sql2005以上肯定支持，sql2000不清楚是否支持）：
with subqry(id,name,pid) as (
select id,name,pid from test1 where id = 5
union all
select test1.id,test1.name,test1.pid from test1,subqry
where test1.pid = subqry.id
)
select * from subqry;
I. SQL語句查詢出父節點下的所有子節點
createtable##tmp_users(idint,usernamenvarchar(255),parentidint)
declare@IDint
select@ID=idfromt_Userstwhereexists
(select*fromt_Userst2wheret2.id=t.parentidandt2.username='user1')
execAddSons@ID
select*from##tmp_users
droptable##tmp_users
--存儲
createprocereAddSons@idint
as
ifexists(select*fromt_Userswhereparentid=@id)
begin
declare@tmp_IDint
declarecurcursorfor
selectidfromt_Userswhereparentid=@id
opencur
fetchnextfromcurinto@tmp_ID
while@@FETCH_STATUS=0
begin
insertinto##tmp_users
select*fromt_Userstwhereid=@tmp_ID
ifexists(select*fromt_Userswhereparentid=@tmp_ID)
begin
execAddSons@tmp_ID
end
fetchnextfromcurinto@tmp_ID
end
closecur
DEALLOCATEcur
end
--遞歸調用，不知道是否想要這樣