SQL Server 父子迭代查询语句，树状查询

来源：互联网发布：绝地求生多核优化编辑：程序博客网时间：2024/06/03 21:33

<pre name="code" class="sql">-- Get childs by parent idWITH TreeAS(    SELECT Id,ParentId FROM dbo.Node P WHERE P.Id = 21 -- parent id    UNION ALL    SELECT C.Id,C.ParentId FROM dbo.Node C    INNER JOIN Tree T ON C.ParentId = T.Id)SELECT * FROM Tree-- Get parents by child idWITH TreeAS(    SELECT Id,ParentId FROM dbo.Node C WHERE C.Id = 57 -- child id    UNION ALL    SELECT P.Id,P.ParentId FROM dbo.Node P    INNER JOIN Tree T ON P.Id = T.ParentId)SELECT * FROM Tree

方案二：带自身的查询所有子类

WITH a AS

SELECT ClassId,ParentId,ClassName FROM dbo.GoodClass <span style="font-family: Arial, Helvetica, sans-serif;">WHERE ParentId=1 </span>

UNION ALL SELECT g.ClassId,g.ParentId,g.ClassName FROM dbo.GoodClass g,a

WHERE g.ParentId=a.ClassId)

SELECT * FROM dbo.GoodClass g WHERE EXISTS(SELECT a.ClassId FROM a WHERE a.ClassId=g.ClassId) UNION ALL SELECT * FROM dbo.GoodClass WHERE ClassId=1ORDER BY g.ParentId,g.classorder

最后用EF调用的时候可以写成一个存储过程

Create PROCEDURE Pro_goods_class_parent_id@ids varchar(500)ASBEGINdeclare @sql varchar(8000)set @sql='WITH Tree  AS  (      SELECT ID,parentId FROM tb_goods_class C WHERE C.Id in ('+@ids+') -- child id      UNION ALL      SELECT P.Id,P.parentId FROM tb_goods_class P      INNER JOIN Tree T ON P.Id = T.parentId  )  SELECT  id  FROM Tree where parentid=0'    SET NOCOUNT ON    EXECUTE(@sql)    SET NOCOUNT OFFENDGO

传递时将子类列表当参数传过来 '1,2,3,4'

0 0