SQL HierarchyId有时会产生错误的路径
问题描述:
我有一个产品列表,使用HierarchyId进行排序,但对于某些项目,层次结构的完整路径产生了错误的结果。只有几...生病下面表明:SQL HierarchyId有时会产生错误的路径
SELECT
ProductId
,Name
,FullName
,Hierarchy
,Hierarchy.ToString() as hierarchyString
,editor.ufn_Product_GetFullName(ProductId) as ufnGetFullName
,Hierarchy.GetLevel() as [level]
FROM Editor.Product
WHERE ProductId = 378
OR Hierarchy.ToString() like '/16/1/1/%'
OR Hierarchy.ToString() = '/16/1/'
OR Hierarchy.ToString() = '/16/'
OR Hierarchy.ToString() = '/'
ORDER BY Hierarchy.ToString()
使用该查询我得到的结果是:
'外部>渲染>颜色>白色'
然而其中四个产生最后两个交换PED像
“外观>渲染>白色>颜色”
我不能为我的生活,找出原因的条款进行交换,为什么只有一些。层次结构看起来正确,并且子级项目上的级别都是正确的。我需要你的帮助......
以下是用于从产品层次结构生成路径的功能,但我不明白它是如何成为问题的。
ALTER FUNCTION [Editor].[ufn_Product_GetFullName] (@ID INT)
RETURNS VARCHAR(8000)
AS BEGIN
-- Create and insert names into temp table
DECLARE @NamesTable TABLE (Name varchar(8000));
INSERT @NamesTable (Name)
SELECT t1.Name
FROM ( SELECT ProductId, Hierarchy, Name
FROM Product WITH(NOLOCK)
WHERE (ProductId = @ID)) AS t2
CROSS JOIN Product AS t1
WHERE (t1.Hierarchy = t2.Hierarchy)
OR (t1.Hierarchy <> '/')
AND (t2.Hierarchy.IsDescendantOf(t1.Hierarchy) = 1)
ORDER BY t1.Hierarchy;
-- Define name string
DECLARE @Name VARCHAR(8000);
-- Coalesce names from temp table into one long string
SELECT @Name = COALESCE(@Name + ' > ', '') + Name
FROM @NamesTable
-- Return full string
RETURN @Name
END
答
没有ORDER BY
条款你不能确定的行的次序。将表Level
添加到@NamesTable
表。将ORDER BY
子句添加到SELECT
您正在拼接字符串的位置。这应该有所帮助。
ALTER FUNCTION [Editor].[ufn_Product_GetFullName] (@ID INT)
RETURNS VARCHAR(8000)
AS BEGIN
-- Create and insert names into temp table
DECLARE @NamesTable TABLE (Name varchar(8000), Level int);
INSERT @NamesTable (Name, Level)
SELECT t1.Name, t1.Hierarchy.getLevel()
FROM ( SELECT ProductId, Hierarchy, Name
FROM Product WITH(NOLOCK)
WHERE (ProductId = @ID)) AS t2
CROSS JOIN Product AS t1
WHERE (t1.Hierarchy = t2.Hierarchy)
OR (t1.Hierarchy <> '/')
AND (t2.Hierarchy.IsDescendantOf(t1.Hierarchy) = 1)
ORDER BY t1.Hierarchy;
-- Define name string
DECLARE @Name VARCHAR(8000);
-- Coalesce names from temp table into one long string
SELECT @Name = COALESCE(@Name + ' > ', '') + Name
FROM @NamesTable
ORDER BY Level
-- Return full string
RETURN @Name
END
这真的是MySQL代码吗? – jarlh