递归查询构造jquery tree

1 现在有如下的一张表：

 

CREATE TABLE [dbo].[ThemeCategory]
(
[ID] [int] NOT NULL,
[ThemeCategoryName] [nvarchar] (250) COLLATE Chinese_PRC_CI_AS NULL,
[ThemeCategoryCode] [nvarchar] (250) COLLATE Chinese_PRC_CI_AS NULL,
[ParentId] [int] NULL,
[OrderId] [int] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ThemeCategory] ADD CONSTRAINT [PK_THEMECATEGORY] PRIMARY KEY CLUSTERED  ([ID]) ON [PRIMARY]
GO

 

其中

ParentId

是它的父ID

现在有一个要求，给某条记录的ID 要递归出它的祖先记录们，也就是倒推回去

 

2 表的内容如下

ID    ThemeCategoryName    ThemeCategoryCode    ParentId    OrderId
1    1    tcfl    0    435
2    2    2    0    43
3    3    3    0    1
4    11    1111    1    1
5    21    21    2    54
6    211    sdfsd    5    342

 

 

3 实现递归遍历的方法

with temp_ThemeCategory ( [Id], [ParentId],[ThemeCategoryName])
as
(
    select [Id], [ParentId],[ThemeCategoryName]
    from PE_C_ThemeCategory
    where ID = @publicThemeId
    union all
    select a.Id, a.parentid,a.ThemeCategoryName
    from ThemeCategory a
    inner join temp_ThemeCategory on a.[id] = temp_ThemeCategory.[ParentId]
) 
select * from temp_ThemeCategory ORDER BY Id ASC

 

 

这样查出来就是