引言
今天有群友问到一个问题，父子结构的数据表，如何展示所有数据？我很自然的想到了使用CTE递归。接着他又提出一个新的问题：能不能显示出级别？大脑检索了半天，没有这方面的思路，因为之前的表结构里通常是会存储这个级别的。但是现在没有，怎么办，有没有办法实现？
知识点
CTE表，（Common Table Expression）是SQL SERVER 2005版本之后引入的一个特性。CTE可以看作是一个临时的结果集，可以在接下来的一个SELECT,INSERT,UPDATE,DELETE,MERGE语句中被多次引用。使用公用表达式可以让语句更加清晰简练。
除此之外，根据微软对CTE好处的描述，可以归结为四点:
• 可以定义递归公用表表达式(CTE)
• 当不需要将结果集作为视图被多个地方引用时，CTE可以使其更加简洁
• GROUP BY语句可以直接作用于子查询所得的标量列
• 可以在一个语句中多次引用公用表表达式(CTE)
案例
参考链接：http://www.cnblogs.com/CareySon/archive/2011/12/12/2284740.html
本站文章版权归原作者及原出处所有 。内容为作者个人观点， 并不代表本站赞同其观点和对其真实性负责。本站是一个个人学习交流的平台，并不用于任何商业目的，如果有任何问题，请及时联系我们，我们将根据著作权人的要求，立即更正或者删除有关内容。本站拥有对此声明的最终解释权。
7 个评论
我这里有个函数案例，你可以看看
USE Table
if OBJECT_ID(N'dbo.F_GetSubGoodsSort',N'FN') is not null drop function dbo.F_GetSubGoodsSort
go
create function dbo.F_GetSubGoodsSort
(
@OrganizationId uniqueidentifier
,@GoodsSortId uniqueidentifier
,@level int
)
returns @sub table
(
ParentId uniqueidentifier
,GoodsSortId uniqueidentifier
,GoodsSortNo nvarchar(100)
,GoodsSortName nvarchar(100)
,Level int
)
AS
BEGIN
;with GoodsSortLevel(ParentId,GoodsSortId,GoodsSortNo,GoodsSortName,Level) AS
(
select A.ParentId
,A.GoodsSortId
,A.GoodsSortNo
,A.GoodsSortName
,0 as Level
from dbo.GoodsSort AS A
where A.OrganizationId =@OrganizationId
and (('00000000-0000-0000-0000-000000000000' = @GoodsSortId and '00000000-0000-0000-0000-000000000000' = A.ParentId) or A.GoodsSortId = @GoodsSortId)
UNION ALL
select C.ParentId
,C.GoodsSortId
,C.GoodsSortNo
,C.GoodsSortName
,Level + 1
from dbo.GoodsSort AS C
join GoodsSortLevel AS TGS
on (C.ParentId = TGS.GoodsSortId )
)
INSERT INTO @sub
(ParentId,GoodsSortId,GoodsSortNo,GoodsSortName,Level)
select ParentId,GoodsSortId,GoodsSortNo,GoodsSortName,Level
from GoodsSortLevel AS TGS
where level < =@level
ORDER BY Level,GoodsSortNo
RETURN
END
GO
Select * From dbo.F_GetSubGoodsSort(@OrganizationId,@GoodsSortId,@level)
还有默认的递归层次是100
USE Table
if OBJECT_ID(N'dbo.F_GetSubGoodsSort',N'FN') is not null drop function dbo.F_GetSubGoodsSort
go
create function dbo.F_GetSubGoodsSort
(
@OrganizationId uniqueidentifier
,@GoodsSortId uniqueidentifier
,@level int
)
returns @sub table
(
ParentId uniqueidentifier
,GoodsSortId uniqueidentifier
,GoodsSortNo nvarchar(100)
,GoodsSortName nvarchar(100)
,Level int
)
AS
BEGIN
;with GoodsSortLevel(ParentId,GoodsSortId,GoodsSortNo,GoodsSortName,Level) AS
(
select A.ParentId
,A.GoodsSortId
,A.GoodsSortNo
,A.GoodsSortName
,0 as Level
from dbo.GoodsSort AS A
where A.OrganizationId =@OrganizationId
and (('00000000-0000-0000-0000-000000000000' = @GoodsSortId and '00000000-0000-0000-0000-000000000000' = A.ParentId) or A.GoodsSortId = @GoodsSortId)
UNION ALL
select C.ParentId
,C.GoodsSortId
,C.GoodsSortNo
,C.GoodsSortName
,Level + 1
from dbo.GoodsSort AS C
join GoodsSortLevel AS TGS
on (C.ParentId = TGS.GoodsSortId )
)
INSERT INTO @sub
(ParentId,GoodsSortId,GoodsSortNo,GoodsSortName,Level)
select ParentId,GoodsSortId,GoodsSortNo,GoodsSortName,Level
from GoodsSortLevel AS TGS
where level < =@level
ORDER BY Level,GoodsSortNo
RETURN
END
GO
Select * From dbo.F_GetSubGoodsSort(@OrganizationId,@GoodsSortId,@level)
还有默认的递归层次是100
有本书《Micrsoft SQL Server 技术内幕 T-SQL 查询》你可以看看哈！
Oracle的递归可以用SYS_CONNECT_BY_PATH求出 递归路径
SQL Server的递归CTE可以实现类似效果吗？
SQL Server的递归CTE可以实现类似效果吗？
自己摸出答案来了
set statistics IO on;
WITH Employees AS
(
SELECT EmployeeKey,
ParentEmployeeKey,
FullName,
Title,
1 AS EmployeeLevel,
CAST(FullName as nvarchar(1000)) as namepath
FROM DimEmployee
WHERE ParentEmployeeKey IS NULL
UNION ALL
SELECT e.EmployeeKey,
e.ParentEmployeeKey,
e.FullName,
e.Title,
es.EmployeeLevel + 1 AS EmployeeLevel,
CAST(es.namepath +'->'+e.fullname as nvarchar(1000)) as namepath
FROM DimEmployee AS e
INNER JOIN Employees AS es
ON es.EmployeeKey = e.ParentEmployeeKey
)
SELECT *
FROM Employees
ORDER BY EmployeeLevel,FullName;
set statistics IO on;
WITH Employees AS
(
SELECT EmployeeKey,
ParentEmployeeKey,
FullName,
Title,
1 AS EmployeeLevel,
CAST(FullName as nvarchar(1000)) as namepath
FROM DimEmployee
WHERE ParentEmployeeKey IS NULL
UNION ALL
SELECT e.EmployeeKey,
e.ParentEmployeeKey,
e.FullName,
e.Title,
es.EmployeeLevel + 1 AS EmployeeLevel,
CAST(es.namepath +'->'+e.fullname as nvarchar(1000)) as namepath
FROM DimEmployee AS e
INNER JOIN Employees AS es
ON es.EmployeeKey = e.ParentEmployeeKey
)
SELECT *
FROM Employees
ORDER BY EmployeeLevel,FullName;