Biegral Blog

<pre name="code" class="sql">-- Get childs by parent id
WITH Tree
AS
(
    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 id
WITH Tree
AS
(
    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=1
ORDER BY g.ParentId,g.classorder

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

Create PROCEDURE Pro_goods_class_parent_id
@ids varchar(500)
AS
BEGIN
declare @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 OFF

END
GO