急求一条查询SQL语句---父类和子类关系的
现有一个类别表A包含栏位如下：
ClassID,ClassName, Validate, ParentClassID
如果记录里的SELECT * FROM A WHERE ClassID=@ClassID AND ISNULL(ParentClassID,'')='' 有值表示该ClassID为最外层的父类别
如果SELECT * FROM A WHERE ParentClassID=@ClassID 没有查询到结果表示该ClassID 为最底层的子类别。
现求一SQL实现下述功能：
对于一个ClassID,它既不是最外层的父类别,也不是最底层的子类别，要求查询出该ClassID下的所有最底层的子类别
比如说类别001 的父类别是000，它下面有类别002,003,004,005,；004下面又有006,007。007下面又有008
那么我要得到的001下面的所有最底层子类别应该为002,003，005,006,008
求高手指点呀，尽量不要使用游标。
[解决办法]
--参考查询指定父节点下的所有子节点：USE tempdbGO-- 建立演示环境CREATE TABLE Dept( id int PRIMARY KEY, parent_id int, name nvarchar(20))INSERT DeptSELECT 0, 0, N'<全部>' UNION ALLSELECT 1, 0, N'财务部' UNION ALLSELECT 2, 0, N'行政部' UNION ALLSELECT 3, 0, N'业务部' UNION ALLSELECT 4, 0, N'业务部' UNION ALLSELECT 5, 4, N'销售部' UNION ALLSELECT 6, 4, N'MIS' UNION ALLSELECT 7, 6, N'UI' UNION ALLSELECT 8, 6, N'软件开发' UNION ALLSELECT 9, 8, N'内部开发'GO-- 查询指定部门下面的所有部门DECLARE @Dept_name nvarchar(20)SET @Dept_name = N'MIS';WITHDEPTS 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 DEPTSGO-- 删除演示环境DROP TABLE Dept----CTE的综合应用USE tempdbGO-- 建立演示环境CREATE TABLE Dept( id int PRIMARY KEY, parent_id int, name nvarchar(20))INSERT DeptSELECT 0, 0, N'<全部>' UNION ALLSELECT 1, 0, N'财务部' UNION ALLSELECT 2, 0, N'行政部' UNION ALLSELECT 3, 0, N'业务部' UNION ALLSELECT 4, 0, N'业务部' UNION ALLSELECT 5, 4, N'销售部' UNION ALLSELECT 6, 4, N'MIS' UNION ALLSELECT 7, 6, N'UI' UNION ALLSELECT 8, 6, N'软件开发' UNION ALLSELECT 9, 8, N'内部开发'GO-- 查询指定部门下面的所有部门, 并汇总各部门的下级部门数DECLARE @Dept_name nvarchar(20)SET @Dept_name = N'MIS';WITHDEPTS 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_idGO-- 删除演示环境DROP TABLE Dept
[解决办法]
if OBJECT_ID('tempdb..#t_class') is not nulldrop table #t_classcreate table #t_class(cid int, cname varchar(10), p_cid int)insert into #t_classselect 1, 'Class1', null union allselect 2, 'Class2', 1 union allselect 3, 'Class3', 1 union allselect 4, 'Class4', 2 union allselect 5, 'Class5', 2 union allselect 6, 'Class6', 3 union allselect 7, 'Class7', 3 union allselect 8, 'Class8', 5 ;With cte as( select cid,cname,p_cid,0 lvl, cast('/'+cname as nvarchar) pth from #t_class where p_cid is null union all select e.cid,e.cname,e.p_cid,c.lvl+1, cast(c.pth + '/' + e.cname as nvarchar) pth from cte c inner join #t_class e on c.cid = e.p_cid)select * from ctewhere pth like '%Class2%' --找出class2下的班级
[解决办法]
/*标题：获取某个节点所有的最底层节点作者：爱新觉罗·毓华(十八年风雨,守得冰山雪莲花开) 时间：2009-07-16地点：新疆乌鲁木齐*//*有一个表名为：tb 结构如下： id --int parentid --int 上层的父节点 sname --varchar(50) 如有以下数据： id parentid sname 1 -1 根节点 2 1 节点1 3 1 节点2 4 2 节点3 5 2 节点4 6 3 节点5 7 3 节点6 7 6 节点7 假如：我要得到某个节点下所有的最底层的节点数据怎么实现： 如：我要得到id=3 的所有最底层的数据是： id parentid sname 7 3 节点6 7 6 节点7 请大家多多帮忙，谢谢！ */create table tb(id int, parentid int, sname varchar(10))insert into tb values(1 , -1 , '根节点') insert into tb values(2 , 1 , '节点1') insert into tb values(3 , 1 , '节点2') insert into tb values(4 , 2 , '节点3') insert into tb values(5 , 2 , '节点4') insert into tb values(6 , 3 , '节点5') insert into tb values(7 , 3 , '节点6') insert into tb values(7 , 6 , '节点7') go--查询指定节点及其所有子节点的函数 CREATE FUNCTION f_Cid(@ID int) RETURNS @t_Level TABLE(ID int,Level int) AS BEGIN DECLARE @Level int SET @Level=1 INSERT @t_Level SELECT @ID,@Level WHILE @@ROWCOUNT>0 BEGIN SET @Level=@Level+1 INSERT @t_Level SELECT a.ID,@Level FROM tb a,@t_Level b WHERE a.parentid=b.ID AND b.Level=@Level-1 END RETURN END GO SELECT distinct a.* FROM tb a,f_Cid(3) b WHERE a.ID=b.ID and a.id not in (select parentid from tb)drop table tbdrop function dbo.f_cid/*id parentid sname ----------- ----------- ---------- 7 3 节点67 6 节点7（所影响的行数为 2 行）*/