给定类别@categoryId，查询应以递归方式导航到最高级的超级类别，这已完成。
现在，我还想一直生成一个字符串，该字符串将是流程中所有CategoryName的串联。
DECLARE @CategoryId AS int = 217;
WITH Categories AS
(
SELECT ParentCategoryId, CategoryName, '' AS strCategory
FROM Category
WHERE CategoryId = @CategoryId
UNION ALL
SELECT c.ParentCategoryId, c.CategoryName,
(c.CategoryName + ': ' + cts.strCategory) AS strCategory
FROM Category AS c
JOIN Categories AS cts
ON c.CategoryId = cts.ParentCategoryId
)
SELECT TOP 1 CategoryName, LEN(CategoryName) AS strLength
FROM Categories
ORDER BY strLength DESC
使用上面的代码，我得到以下错误：
Types don't match between the anchor and the recursive part in column
"strCategory" of recursive query "Categories".
感谢您的帮助
尝试更改查询以将varchars强制转换为VARCHAR（MAX）。
就像是
DECLARE @CategoryId AS int = 217;
WITH Categories AS
(
SELECT ParentCategoryId, CategoryName, CAST('' AS VARCHAR(MAX)) AS strCategory
FROM Category
WHERE CategoryId = @CategoryId
UNION ALL
SELECT c.ParentCategoryId, c.CategoryName,
CAST((c.CategoryName + ': ' + cts.strCategory) AS VARCHAR(MAX)) AS strCategory
FROM Category AS c
JOIN Categories AS cts
ON c.CategoryId = cts.ParentCategoryId
)
SELECT TOP 1 CategoryName, LEN(CategoryName) AS strLength
FROM Categories
ORDER BY strLength DESC
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句