2010-10-11
sql server Common Table Expressions(CTE) 实现递归查询

WITH CTEArea([ID],[Code],[Name],[level]) as
(
    SELECT [ID],[Code],[Name],1 as [level] FROM [WMS_Area] WHERE [ParentID] ='00000000-0000-0000-0000-000000000000' and [Name]='江苏省'
    UNION ALL
    SELECT a.[ID],a.[Code],a.[Name],b.[level]+1 FROM [WMS_Area] as a,CTEArea as b
    where a.[ParentID] = b.[ID]
)
select [ID],[Code],[Name],[level] from CTEArea
order by [Code]

posted @ 2010-10-11 15:20 liuwei0514 Views(12) Comments(0) Edit