表字段
F_ID,F_name,F_parentID
1,名字,0
2,mm,1
3,nn,2
4,bb,3
·
·
·求SQL查询父节点下的所有子节点(包括子节点下的子节点,子节点可能还有子节点····),
而我的SQL语句 select b.* from table as a ,table as b where a.F_ID =b.F_parentID and a.F_parentID='1' or (a.F_ID=b.F_parentID and a.F_ID='1') 只能查到 “3,nn,2”。不能把所有的子节点下的所有子节点都查询出来。求大牛帮忙解答,可以把父节点下所有子节点都查询出来的SQL语句。
F_ID,F_name,F_parentID
1,名字,0
2,mm,1
3,nn,2
4,bb,3
·
·
·求SQL查询父节点下的所有子节点(包括子节点下的子节点,子节点可能还有子节点····),
而我的SQL语句 select b.* from table as a ,table as b where a.F_ID =b.F_parentID and a.F_parentID='1' or (a.F_ID=b.F_parentID and a.F_ID='1') 只能查到 “3,nn,2”。不能把所有的子节点下的所有子节点都查询出来。求大牛帮忙解答,可以把父节点下所有子节点都查询出来的SQL语句。
--2005 cte;with cte as
(
select fid,fparentid from tb where fid = 3 --查询节点
union all
select a.fid,a.fparentid
from tb a join tb b on a.fparentid = b.id
where a.id is not null
)select * from cte
CREATE FUNCTION [dbo].[fn_getsubtree](@empid AS INT)
RETURNS @TREE TABLE
(
empid INT NOT NULL
,empname VARCHAR(25) NOT NULL
,mgrid INT NULL
,lvl INT NOT NULL
)
AS
BEGIN
WITH Employees_Subtree(empid, empname, mgrid, lvl)
AS
(
SELECT empid, empname, mgrid, 0
FROM Employees
WHERE empid = @empid UNION all
SELECT e.empid, e.empname, e.mgrid, es.lvl+1
FROM Employees AS e
JOIN Employees_Subtree AS es
ON e.mgrid = es.empid
)
INSERT INTO @TREE
SELECT * FROM Employees_Subtree; RETURN
END
我是用Firebird数据库的,这个函数无法录入 所以不能实现
-->Title:Generating test data
-->Author:wufeng4552
-->Date :2009-09-30 08:52:38
set nocount on
if object_id('tb','U')is not null drop table tb
go
create table tb(ID int, ParentID int)
insert into tb select 1,0
insert into tb select 2,1
insert into tb select 3,1
insert into tb select 4,2
insert into tb select 5,3
insert into tb select 6,5
insert into tb select 7,6
-->Title:查找指定節點下的子結點
if object_id('Uf_GetChildID')is not null drop function Uf_GetChildID
go
create function Uf_GetChildID(@ParentID int)
returns @t table(ID int)
as
begin
insert @t select ID from tb where ParentID=@ParentID
while @@rowcount<>0
begin
insert @t select a.ID from tb a inner join @t b
on a.ParentID=b.id and
not exists(select 1 from @t where id=a.id)
end
return
end
go
select * from dbo.Uf_GetChildID(5)
/*
ID
-----------
6
7
*/
-->Title:查找指定節點的所有父結點
if object_id('Uf_GetParentID')is not null drop function Uf_GetParentID
go
create function Uf_GetParentID(@ID int)
returns @t table(ParentID int)
as
begin
insert @t select ParentID from tb where ID=@ID
while @@rowcount!=0
begin
insert @t select a.ParentID from tb a inner join @t b
on a.id=b.ParentID and
not exists(select 1 from @t where ParentID=a.ParentID)
end
return
end
go
select * from dbo.Uf_GetParentID(2)
/*
ParentID
-----------
1
0
*/
USE tempdb
GO-- 建立演示环境
CREATE TABLE Dept(
id int PRIMARY KEY,
parent_id int,
name nvarchar(20))
INSERT Dept
SELECT 0, 0, N'<全部>' UNION ALL
SELECT 1, 0, N'财务部' UNION ALL
SELECT 2, 0, N'行政部' UNION ALL
SELECT 3, 0, N'业务部' UNION ALL
SELECT 4, 0, N'业务部' UNION ALL
SELECT 5, 4, N'销售部' UNION ALL
SELECT 6, 4, N'MIS' UNION ALL
SELECT 7, 6, N'UI' UNION ALL
SELECT 8, 6, N'软件开发' UNION ALL
SELECT 9, 8, N'内部开发'
GO-- 查询指定部门下面的所有部门
DECLARE @Dept_name nvarchar(20)
SET @Dept_name = N'MIS'
;WITH
DEPTS AS(
-- 定位点成员
SELECT * FROM Dept
WHERE name = @Dept_name
UNION ALL
-- 递归成员, 通过引用CTE自身与Dept基表JOIN实现递归
SELECT A.*
FROM Dept A, DEPTS B
WHERE A.parent_id = B.id
)
SELECT * FROM DEPTS
GO-- 删除演示环境
DROP TABLE Dept----CTE的综合应用USE tempdb
GO-- 建立演示环境
CREATE TABLE Dept(
id int PRIMARY KEY,
parent_id int,
name nvarchar(20))
INSERT Dept
SELECT 0, 0, N'<全部>' UNION ALL
SELECT 1, 0, N'财务部' UNION ALL
SELECT 2, 0, N'行政部' UNION ALL
SELECT 3, 0, N'业务部' UNION ALL
SELECT 4, 0, N'业务部' UNION ALL
SELECT 5, 4, N'销售部' UNION ALL
SELECT 6, 4, N'MIS' UNION ALL
SELECT 7, 6, N'UI' UNION ALL
SELECT 8, 6, N'软件开发' UNION ALL
SELECT 9, 8, N'内部开发'
GO-- 查询指定部门下面的所有部门, 并汇总各部门的下级部门数
DECLARE @Dept_name nvarchar(20)
SET @Dept_name = N'MIS'
;WITH
DEPTS AS( -- 查询指定部门及其下的所有子部门
-- 定位点成员
SELECT * FROM Dept
WHERE name = @Dept_name
UNION ALL
-- 递归成员, 通过引用CTE自身与Dept基表JOIN实现递归
SELECT A.*
FROM Dept A, DEPTS B
WHERE A.parent_id = B.id
),
DEPTCHILD AS( -- 引用第1个CTE,查询其每条记录对应的部门下的所有子部门
SELECT
Dept_id = P.id, C.id, C.parent_id
FROM DEPTS P, Dept C
WHERE P.id = C.parent_id
UNION ALL
SELECT
P.Dept_id, C.id, C.parent_id
FROM DEPTCHILD P, Dept C
WHERE P.id = C.parent_id
),
DEPTCHILDCNT AS( -- 引用第2个CTE, 汇总得到各部门下的子部门数
SELECT
Dept_id, Cnt = COUNT(*)
FROM DEPTCHILD
GROUP BY Dept_id
)
SELECT -- JOIN第1,3个CTE,得到最终的查询结果
D.*,
ChildDeptCount = ISNULL(DS.Cnt, 0)
FROM DEPTS D
LEFT JOIN DEPTCHILDCNT DS
ON D.id = DS.Dept_id
GO-- 删除演示环境
DROP TABLE Dept
WITH RECURSIVE
DEPT_TREE AS
(
SELECT * FROM table
WHERE F_ID = '1'
UNION ALL
SELECT * FROM tbale D
JOIN DEPT_TREE H
ON D.F_parentID = H.F_ID
)
SELECT *
FROM DEPT_TREEfirebird中用这个查询语句就做到了树的递归查询,so强大
WITH RECURSIVE
DEPT_TREE AS
(
SELECT * FROM t_client
WHERE F_ID = '111'
UNION ALL
SELECT * FROM T_Client D
JOIN DEPT_TREE H
ON D.F_Superior = H.F_ID
)
SELECT *
FROM DEPT_TREE在firebird中用上面这个查询语句就能做到tree的递归查询,把父节点的所有子节点都查出来。