SQL SERVER 在递归查询 "temp" 的列 "层级路径" 中,定位点类型和递归部分的类型不匹配。
今天练习sql server 递归(with as)的时候,发现的问题, “定位点类型和递归部分的类型不匹配”,特此记录。详细原因在下↓
练习sql脚本
CREATE TABLE #TE
(ID INT , -- 职员ID 唯一值
USERNAME VARCHAR(20), --职员姓名
JOB VARCHAR(20), --职位角色
MONEYS decimal(18,2), --提成金额
F_ID INT --上级职员ID
)
INSERT INTO #TE
SELECT 1,'总监董','总监',0,0
INSERT INTO #TE
SELECT 2,'张三','一级员工',20,1
INSERT INTO #TE
SELECT 3,'李四','一级员工',25,1
INSERT INTO #TE
SELECT 4,'王二','二级员工',10,2
INSERT INTO #TE
SELECT 5,'麻子','三级员工',50,3
INSERT INTO #TE
SELECT 6,'傻子','四级员工',50,4
INSERT INTO #TE
SELECT 7,'总监张','总监',0,0
INSERT INTO #TE
SELECT 8,'一','一级员工',20,7
INSERT INTO #TE
SELECT 9,'二','一级员工',25,7
INSERT INTO #TE
SELECT 10,'三','二级员工',21,8
select * from #TE;
with temp as
(
select
*,
cast(id as nvarchar(max)) 层级路径,
cast(0 as decimal(18,4)) 提成,
cast(id as varchar(50)) 总监id
from #TE
where job='总监'
union all
select
a.*,
B.层级路径 + '->' + CAST(A.ID AS nvarchar(max)) AS 层级路径,
cast((a.moneys * 0.02) as decimal(18,4)) 提成,
b.总监id
from #TE a
inner join temp b on a.f_id=b.id
)
select * from temp
错误示例::
一开始以为是上下两边类型不对,特地给了 cast 转换相同ncarchar(500)类型,然后发现还是不行,后来看了正确答案是ncarchar(max),改了以后确实不报错可以执行了,不明白原理所以去搜了一下原因。
修改正确以后:
原因:
由于字段 “层级路径” 涉及无限递归循环,所以需要一个很大的字符串长度,所以将字符串长度设置为max或4000即可