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
这样查出来就是