具有唯一标识符的递归 CTE 必须在锚点中具有值 [英] Recursive CTE with uniqueidentifier must have value in an anchor

问题描述

;WITH Companies(pkCompanyID,name,parentCompanyID,SomeId) AS

        (
            SELECT
                tblCompany.pkCompanyID,
                tblCompany.name,
                NULL,--this is a int with no value and it works in the anchor
                NULL AS SomeId--But this uniqueidentifier do not work why?
            FROM
                tblCompany
            WHERE
                tblCompany.fkCompToCompID IS NULL
            UNION ALL
            SELECT
                tblCompany.pkCompanyID,
                tblCompany.name,
                tblCompany.fkCompToCompID,
                NEWID()
            FROM
                tblCompany
                JOIN Companies ON tblCompany.fkCompToCompID=Companies.pkCompanyID
        )
        SELECT
            *
        FROM
            Companies

所以这个函数会给出错误:类型在递归查询公司"的SomeId"列中的锚点和递归部分之间不匹配".我知道我可以通过将NULL AS SomeId"替换为CAST(NULL AS uniqueidentifier)"来修复它.但是为什么当 parentCompanyId 不需要知道它是一个 int 时，anchor 需要知道它是一个 uniqueidentifier 呢?是不是因为uniqueidentifier是对象而int是值类型?

So this function will give the error: "Types don't match between the anchor and the recursive part in column "SomeId" of recursive query "Companies"". I know that I can fix it with just replace "NULL AS SomeId" with "CAST(NULL AS uniqueidentifier)". But why do the anchor need to know that it is a uniqueidentifier when the parentCompanyId don't need to know that it is an int? Is it because a uniqueidentifier is a object and the int is a value type?

推荐答案

NULL 默认假定为 int 这就是为什么你不必强制转换那个明确的.

NULL is assumed by default to be an int which is why you don't have to cast that one explicitly.

你可以看到这个

SELECT NULL AS FOO INTO BAR;

SELECT DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME='FOO'AND TABLE_NAME='BAR';

这篇关于具有唯一标识符的递归 CTE 必须在锚点中具有值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！