sql server 2008 r2-sql层次结构ID-返回属于匹配查询项的后代和祖先

Question

感谢您抽出时间阅读这篇文章，非常感谢您的帮助。

我需要帮助创建一个SQL查询，该查询返回与描述匹配或包含我的WHERE查询字符串的项的所有祖先和后代。

我的桌子是这样的：

ID
层次结构名称1/产品2/1/辆3/1/1/红色4/1/2/蓝色5/2/自行车6/2/1/绿色7/2/2/Red

我想按描述进行搜索，所有匹配项都应该连同它们的后代和祖先一起返回。

例如，搜索词="红色">

结果

ID
层次结构名称1/产品2/1/辆3/1/1/红色5/2/自行车7/2/2/红色

另一个例子：

搜索词='bi'

结果(因为bi包含在自行车字符串中

ID
层次结构名称1/产品5/2/自行车6/2/1/绿色7/2/2/红色

非常感谢，

Rob

**********2014年3月28日编辑

我几乎满足了以下问题。然而，它只检索所有祖先，而不是后代。

WITH Ancestors(层次结构、[Name]、AncestorId)AS(选择层次结构，[名称]，层次结构。GetAncestor(1)来自dbo。SpecProducts其中Name="Chrome"--或者选择该节点所需的任何内容联合所有选择ht.Herarchy，ht.[Name]，ht.Herachy.GetAncestor(1)来自dbo。规格产品ht内部联接Ancestors a ON-ht.Hierarchy=a.AncestorId)SELECT DISTINCT*，层次结构。ToString()来自祖先

*****2014年3月7日编辑

@Yousseff DAOUI-作为对您的回答的回应，我很难将您的代码转换为使用我的表。下面是我尝试让你的代码与我的表一起工作：

声明@p_name NVARCHAR='Gr'；WITH Temp AS(SELECT*FROM SpecProducts)--DB表，Temp_parents AS(SELECT ID，层次结构，名称--，0级别(_L)来自临时雇员其中名称LIKE"%"+@p_Name+"%"联合所有SELECT tab.ID，tab.Herarchy，tab.Name--，p.Level+1来自"温度"选项卡内部联接Temp_parents pON p.Hierarchy LIKE选项卡。层次结构+'_/')，临时助理(SELECT ID，层次结构，名称--，0级别(_L)来自临时雇员其中名称LIKE"%"+@p_Name+"%"联合所有选择选项卡.ID，选项卡.层次结构，选项卡.名称--，d._Level-1来自"温度"选项卡内部联接时间_场景dON选项卡。层次结构LIKE。层次结构+'_/')选择*FROM Temp_parents活接头选择*FROM Temp_descendants

注意第3行如何尝试从名为SpecProducts:的表中提取数据

WITH Temp AS (SELECT * FROM SpecProducts) -- DB Table

然而，当我尝试执行代码时，MS SQL Server Management Studio一直输出以下错误：

消息403，级别16，状态1，第3行数据类型的运算符无效。运算符等于加法，类型等于hierarchyid

你知道我该怎么做吗？

非常感谢。

**第二次编辑-2014年3月7日*

这看起来有点好，但查询确实很慢(我认为它拉的行比应该拉的要多)。是因为HierarchyID"/"正在返回，因此正在查找根"/"的所有子级，这就是一切吗？

DECLARE@p_name NVARCHAR='壁纸'；WITH Temp AS(SELECT*FROM SpecProducts)--DB表，Temp_parents AS(SELECT ID，层次结构，名称--，0级别(_L)来自临时雇员其中名称LIKE"%"+@p_Name+"%"联合所有SELECT tab.ID，tab.Herarchy，tab.Name--，p.Level+1来自"温度"选项卡内部联接Temp_parents pON p.Hierarchy.ToString()LIKE选项卡.Herarchy.To String()+'_/')，临时助理(SELECT ID，层次结构，名称--，0级别(_L)来自临时雇员其中名称LIKE"%"+@p_Name+"%"联合所有选择选项卡.ID，选项卡.层次结构，选项卡.名称--，d._Level-1来自"温度"选项卡内部联接时间_场景dON选项卡.Herarchy.ToString()LIKE d.层次结构.ToString()+'_/')选择*FROM Temp_parents活接头选择*自时间场景

****第三版-2014年3月7日

好的，真的很抱歉编辑的次数。但现在这似乎是基于优素福的代码实现的。

DECLARE@p_name NVARCHAR(255)="自然"；带Temp_parents AS(SELECT ID，层次结构，名称--，0级别(_L)来自SpecProducts其中名称LIKE"%"+@p_Name+"%"联合所有SELECT tab.ID，tab.Herarchy，tab.Name--，p.Level+1来自SpecProducts选项卡内部联接Temp_parents pON p.Hierarchy.ToString()LIKE选项卡.Herarchy.To String()+'_/')，临时助理(SELECT ID，层次结构，名称--，0级别(_L)来自SpecProducts其中名称LIKE"%"+@p_Name+"%"联合所有选择选项卡.ID，选项卡.层次结构，选项卡.名称--，d._Level-1来自SpecProducts选项卡内部联接时间_场景dON选项卡.Herarchy.ToString()LIKE d.层次结构.ToString()+'_/')选择*FROM Temp_parents活接头选择*FROM Temp_descendants

Answer 1

我有一个解决方案给你，我希望它能帮助你，下面是代码：

DECLARE @p_name NVARCHAR(255) = 'b'

IF OBJECT_ID('Temp') IS NOT NULL
DROP TABLE Temp
IF OBJECT_ID('Temp_parents') IS NOT NULL
DROP TABLE Temp_parents
IF OBJECT_ID('Temp_descendants') IS NOT NULL
DROP TABLE Temp_descendants
SELECT *
INTO Temp
FROM  (
SELECT 1 AS ID, '/' AS Hierarchy, 'Products' Name UNION ALL
SELECT 2 AS ID, '/1/' AS Hierarchy, 'Cars' Name UNION ALL
SELECT 3 AS ID, '/1/1/' AS Hierarchy, 'Red' Name UNION ALL
SELECT 4 AS ID, '/1/2/' AS Hierarchy, 'Blue' Name UNION ALL
SELECT 5 AS ID, '/2/' AS Hierarchy, 'Bike' Name UNION ALL
SELECT 6 AS ID, '/2/1/' AS Hierarchy, 'Green' Name UNION ALL
SELECT 7 AS ID, '/2/2/' AS Hierarchy, 'Red' Name) AS T

DECLARE @v_name_1 NVARCHAR(255)= @p_name;
WITH parents AS (
SELECT ID, Hierarchy, Name, 0 _Level
FROM
Temp
WHERE
Name LIKE '%'+@v_name_1+'%'
UNION ALL
SELECT tab.ID, tab.Hierarchy, tab.Name, p._Level+1
FROM
Temp tab
INNER JOIN
parents p
ON p.Hierarchy LIKE tab.Hierarchy+'_/')

select ID, Hierarchy, Name
INTO Temp_parents
from parents

DECLARE @v_name_2 NVARCHAR(255)= @p_name;
WITH descendants AS (
SELECT ID, Hierarchy, Name, 0 _Level
FROM
Temp
WHERE
Name LIKE '%'+@v_name_2+'%'
UNION ALL
SELECT tab.ID, tab.Hierarchy, tab.Name, d._Level-1
FROM
Temp tab
INNER JOIN
descendants d
ON tab.Hierarchy LIKE d.Hierarchy+'_/')

SELECT ID,Hierarchy ,Name
INTO Temp_descendants
FROM
descendants
WHERE
ID IS NOT NULL
SELECT *
FROM Temp_parents
UNION
SELECT *
FROM Temp_descendants

IF OBJECT_ID('Temp') IS NOT NULL
DROP TABLE Temp
IF OBJECT_ID('Temp_parents') IS NOT NULL
DROP TABLE Temp_parents
IF OBJECT_ID('Temp_descendants') IS NOT NULL
DROP TABLE Temp_descendants

好运：)

Answer 2

以下是基于第一种编写代码的另一种方法，使代码看起来更容易：)

DECLARE @p_name NVARCHAR(255) = 'Gr';
WITH Temp AS (
SELECT 1 AS ID, '/' AS Hierarchy, 'Products' Name UNION ALL
SELECT 2 AS ID, '/1/' AS Hierarchy, 'Cars' Name UNION ALL
SELECT 3 AS ID, '/1/1/' AS Hierarchy, 'Red' Name UNION ALL
SELECT 4 AS ID, '/1/2/' AS Hierarchy, 'Blue' Name UNION ALL
SELECT 5 AS ID, '/2/' AS Hierarchy, 'Bike' Name UNION ALL
SELECT 6 AS ID, '/2/1/' AS Hierarchy, 'Green' Name UNION ALL
SELECT 7 AS ID, '/2/2/' AS Hierarchy, 'Red' Name)
,Temp_parents AS (
SELECT ID, Hierarchy, Name --, 0 _Level
FROM
Temp
WHERE
Name LIKE '%'+@p_name+'%'
UNION ALL
SELECT tab.ID, tab.Hierarchy, tab.Name --, p._Level+1
FROM
Temp tab
INNER JOIN
Temp_parents p
ON p.Hierarchy LIKE tab.Hierarchy+'_/')
,Temp_descendants AS (
SELECT ID, Hierarchy, Name --, 0 _Level
FROM
Temp
WHERE
Name LIKE '%'+@p_name+'%'
UNION ALL
SELECT tab.ID, tab.Hierarchy, tab.Name --, d._Level-1
FROM
Temp tab
INNER JOIN
Temp_descendants d
ON tab.Hierarchy LIKE d.Hierarchy+'_/')

SELECT *
FROM Temp_parents
UNION
SELECT *
FROM Temp_descendants

好运

2014年3月7日编辑-为了使用我的表格，我不得不将其修改为以下内容-现在看来效果良好**

DECLARE@p_name NVARCHAR(255)="自然"；带Temp_parents AS(SELECT ID，层次结构，名称--，0级别(_L)来自SpecProducts其中名称LIKE"%"+@p_Name+"%"联合所有SELECT tab.ID，tab.Herarchy，tab.Name--，p.Level+1来自SpecProducts选项卡内部联接Temp_parents pON p.Hierarchy.ToString()LIKE选项卡.Herarchy.To String()+'_/')，临时助理(SELECT ID，层次结构，名称--，0级别(_L)来自SpecProducts其中名称LIKE"%"+@p_Name+"%"联合所有选择选项卡.ID，选项卡.层次结构，选项卡.名称--，d._Level-1来自SpecProducts选项卡内部联接时间_场景dON选项卡.Herarchy.ToString()LIKE d.层次结构.ToString()+'_/')选择*FROM Temp_parents活接头选择*FROM Temp_descendants

Answer 3

可能是这样的：

select id, HierarchyID, Description from (
select b.id, b.HierarchyID, b.Description, 
case 
when (a.id = b.id) then 1 
when (marker = 1 and num_len > 1 and LEN(replace(b.HierarchyID,'/','')) < num_len and numbers > replace(b.HierarchyID,'/','')) then 1
when (marker = 1 and num_len = 1 and LEN(replace(b.HierarchyID,'/','')) > num_len and numbers < replace(b.HierarchyID,'/','')) then 1
else 0 end as marker from (
select id, SUBSTRING(HierarchyID,2,1) as first_num, 1 as marker, replace(HierarchyID,'/','') as numbers, LEN(replace(HierarchyID,'/','')) as num_len
from my_tab where Description like '%bi%' or HierarchyID = '/') a right join my_tab b on first_num = SUBSTRING(b.HierarchyID,2,1)) c where marker = 1;

对不起我的英语！

1. select id，您在HierarchyID中找到的第一个数字("/2/1/"是2)，您找到的要保留的值的"标记"(标记=1)，您可以在HierachyID中发现的完整数字("2/2/1/"为21)，以及您在my_tab表(a)中找到的完整数字的长度，以及您选择的搜索项。

2. 使用(1.)，我们对mytab表(b)进行右联接，以获得所有其他值。第一种情况是：如果(a)的id等于(b)的id，我们取标记1(因为这是我们用搜索项得到的值)。第二种情况是：如果标记为1，并且(a)的长度>1(这意味着我们有一个类似于"/2/1/"的值)，并且(b)的长度<(a)的长度(这意味着"/2/2/"具有标记=1和长度2；"/2/1/"具有相同的长度，但我们不想要他；我们想要"/2/")，并且您可以在层次结构ID(a)中找到的完整数字大于您可以在层级结构ID(b)中找到的完整数字(意味着我们将获得"/2/"值)，我们将标记设置为1。第三种情况当：类似于"第二种情况当"。在这里，我们来看看标记为1、长度为1("/2/")的值，以标记我们想要的所有值，如"/2/1/"one_answers"/2/2/"。

3. 现在，您可以使用标记=1获得所需的所有值。