sql - 如何使用 CTE。有一些与 anchor 相关的错误

Question

我正在创建列的层次结构表示。但是出现错误

详情是

Msg 240, Level 16, State 1, Line 1 Types don't match between the anchor and the recursive part in column "DISPLAY" of recursive query "CTE".

我知道有一些类型转换错误。但我不知道如何消除错误。请不要只解决我的错误。我需要解释为什么会出现此错误。发生此错误时。

我正在尝试根据我介绍的排序列对表格进行排序。我想在每个级别添加“-”并相应地进行排序。

请帮忙

WITH CTE (PK_CATEGORY_ID, [DESCRIPTION], FK_CATEGORY_ID, DISPLAY, SORT, DEPTH)
        AS
        (
            SELECT PK_CATEGORY_ID, [DESCRIPTION], FK_CATEGORY_ID, '-' AS DISPLAY, '--' AS SORT, 0 AS DEPTH 
            FROM dbo.L_CATEGORY_TYPE WHERE FK_CATEGORY_ID IS NULL

            UNION ALL

            SELECT T.PK_CATEGORY_ID, T.[DESCRIPTION], T.FK_CATEGORY_ID, CAST(DISPLAY+T.[DESCRIPTION] AS VARCHAR(1000)), '--' AS SORT, C.DEPTH +1
            FROM dbo.L_CATEGORY_TYPE T JOIN CTE C ON C.PK_CATEGORY_ID = T.FK_CATEGORY_ID

            --SELECT T.PK_CATEGORY_ID, C.SORT+T.[DESCRIPTION], T.FK_CATEGORY_ID
            --, CAST('--' + C.SORT AS VARCHAR(1000)) AS SORT, CAST(DEPTH +1 AS INT) AS DEPTH
            --FROM dbo.L_CATEGORY_TYPE T JOIN CTE C ON C.FK_CATEGORY_ID = T.PK_CATEGORY_ID
        )
        SELECT PK_CATEGORY_ID, [DESCRIPTION], FK_CATEGORY_ID, DISPLAY, SORT, DEPTH 
        FROM CTE            
        ORDER BY SORT

Answer 1

在 SQL Server 中，字符串常量的数据类型为 CHAR，而不是 VARCHAR。

您需要使用显式转换:

WITH CTE (PK_CATEGORY_ID, [DESCRIPTION], FK_CATEGORY_ID, DISPLAY, SORT, DEPTH)
        AS
        (
            SELECT PK_CATEGORY_ID, [DESCRIPTION], FK_CATEGORY_ID, CAST('-' AS VARCHAR(1000)) AS DISPLAY, '--' AS SORT, 0 AS DEPTH 
            FROM dbo.L_CATEGORY_TYPE WHERE FK_CATEGORY_ID IS NULL

            UNION ALL

            SELECT T.PK_CATEGORY_ID, T.[DESCRIPTION], T.FK_CATEGORY_ID, CAST(DISPLAY+T.[DESCRIPTION] AS VARCHAR(1000)), '--' AS SORT, C.DEPTH +1
            FROM dbo.L_CATEGORY_TYPE T JOIN CTE C ON C.PK_CATEGORY_ID = T.FK_CATEGORY_ID

            --SELECT T.PK_CATEGORY_ID, C.SORT+T.[DESCRIPTION], T.FK_CATEGORY_ID
            --, CAST('--' + C.SORT AS VARCHAR(1000)) AS SORT, CAST(DEPTH +1 AS INT) AS DEPTH
            --FROM dbo.L_CATEGORY_TYPE T JOIN CTE C ON C.FK_CATEGORY_ID = T.PK_CATEGORY_ID
        )
        SELECT PK_CATEGORY_ID, [DESCRIPTION], FK_CATEGORY_ID, DISPLAY, SORT, DEPTH 
        FROM CTE            
        ORDER BY SORT