tsql - 当 NULL anchor 在 NOT NULL 字段上重复出现时，如何解决 CTE 中的类型不匹配问题

Question

在最终将提供 SSRS RDL 文档的 SQL 脚本中，我尝试利用 CTE 递归地获取我的数据。

实际的 SQL 代码太大了，无法复制粘贴到这里，但我做了这个简短的重现:

declare @temp table
(
id uniqueidentifier not null default(newid()),
name varchar(100) not null default('new'),
value varchar(100) not null default('newValue'),
parentid uniqueidentifier null
)

insert into @temp (id, name, value, parentid) values ('12312312-1234-1234-1234-123412341234', 'o1', 'val1', null)
insert into @temp (id, name, value, parentid) values ('12312312-1234-1234-1234-123412341235', 'o2', 'val2', '12312312-1234-1234-1234-123412341234')
insert into @temp (id, name, value, parentid) values ('12312312-1234-1234-1234-123412341236', 'o3', 'val3', '12312312-1234-1234-1234-123412341234')

;with
    cte(id,name, value, pid, pname, pvalue) as (
        select id, name, value, null, null, null from @temp where parentid is null
        union all
        select r.id, r.name, r.value, a.id, a.name, a.value 
        from @temp r inner join cte a
        on a.id = r.parentid
    )
    select * from cte

CTE 错误如下(在 SSMS 2012 中测试):

Msg 240, Level 16, State 1, Line 13
Types don't match between the anchor and the recursive part in column "pid" of recursive query "cte".
Msg 240, Level 16, State 1, Line 13
Types don't match between the anchor and the recursive part in column "pname" of recursive query "cte".
Msg 240, Level 16, State 1, Line 13
Types don't match between the anchor and the recursive part in column "pvalue" of recursive query "cte".

我很确定这是由于在 anchor 部分选择了 NULL 值而不是在递归部分获取的 NOT NULL 字段......这个问题可以解决吗？

(我愿意接受完成此任务的其他方法。)

Answer 1

转换文字 null 值以匹配。

;with
cte(id,name, value, pid, pname, pvalue) as (
    select id, name, value, cast(null as uniqueidentifier),
        cast(null as varchar(100)), cast(null as varchar(100))
    from @temp where parentid is null
    union all
    select r.id, r.name, r.value, a.id, a.name, a.value 
    from @temp r inner join cte a
    on a.id = r.parentid
)

否则，SQL 将为每个 null 假定默认类型 int，这将不匹配联合的第二部分。