T-SQL学习记录：公用表表达式求递归路径

发表: 2016-05-05 浏览: 1745

SQL

引言：

Oracle的PLSQL有现成的递归函数并可以显示级别和递归路径，但是SQL Server下如何实现呢？看了一些资料后，知道用CTE可以实现，可是我还想获取到递归路径（PLSQL下使用SYS_CONNECT_BY_PATH获取）

问题：

WITH Employees AS 

(

SELECT EmployeeKey,

ParentEmployeeKey,

FullName,

Title,

1 AS EmployeeLevel,

FullName as namepath

FROM DimEmployee

WHERE ParentEmployeeKey IS NULL

UNION ALL

SELECT e.EmployeeKey,

e.ParentEmployeeKey,

e.FullName,

e.Title,

es.EmployeeLevel + 1 AS EmployeeLevel,

es.namepath +'->'+e.fullname as namepath

FROM DimEmployee AS e

INNER JOIN Employees AS es

ON es.EmployeeKey = e.ParentEmployeeKey 

)

SELECT *

FROM Employees

ORDER BY EmployeeLevel,FullName;

报错：

消息 240，级别 16，状态 1，第 1 行

在递归查询 "Employees" 的列 "namepath" 中，定位点类型和递归部分的类型不匹配。

解决办法：

1、对列做显式类型转换；

2、with中第一条select的字段长度一定要足够长。

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;