如何在 SQL Server 中将层次关系分组在一起 [英] How to group hierarchical relationships together in SQL Server
问题描述
我在表示例中有一个列名父和子，下面是表数据
I have a column name Parent and Child in table Example and Below is the Table Data
| Parent | Child |
|---------------------|------------------|
| 100 | 101 |
|---------------------|------------------|
| 101 | 102 |
|---------------------|------------------|
| 200 | 201 |
|---------------------|------------------|
| 103 | 102 |
|---------------------|------------------|
| 202 | 201 |
|---------------------|------------------|
如果我输入 100 我应该得到的结果是 100,101,102,103 因为 100->101->102->103 和此外，如果我将输入设为 102，那么它应该给出与上述相同的结果.102->101->100 和 102->103.我只需要使用存储过程来实现这一点.
If i give the input as 100 i should get the result as 100,101,102,103 Since 100->101->102->103 and also if i give the input as 102 then it should give the same above result. 102->101->100 and 102->103. I need to achieve this using stored Procedure only.
下面是我正在尝试的示例代码
Below is the sample Code which i am trying
CREATE PROCEDURE GetAncestors(@thingID varchar(MAX))
AS
BEGIN
SET NOCOUNT ON;
WITH
CTE
AS
(
SELECT
Example.Parent, Example.Child
FROM Example
WHERE Parent = @thingID or Child = @thingID
UNION ALL
SELECT
Example.Parent, Example.Child
FROM
CTE
INNER JOIN Example ON Example.Parent = CTE.Child
)
SELECT
Parent AS Result
FROM CTE
UNION
SELECT
Child AS Result
FROM CTE
;
END
GO
推荐答案
您尝试的问题是在开始时进行过滤.如果我是对的，您希望通过它们的关系(上升或下降或它们的混合)对数据进行聚类(将它们组合在一起).例如 ID 100 有子 101，它有另一个子 102，但 102 有父 103 并且您希望该集合中的任何输入的结果都是这四个 (100, 101, 102, 103).这就是您无法在一开始就进行过滤的原因，因为您无法知道哪个关系将链接到另一个关系中.
The problem with your attempt is filtering at the start. If I'm right, your want to cluster your data (group them all together) by their relationships, either ascendant or descendant, or a mix of them. For example ID 100 has child 101, which has another child 102, but 102 has a parent 103 and you want the result to be these four (100, 101, 102, 103) for any input that is in that set. This is why you can't filter at the start, since you don't have any means of knowing which relationship will be chained throughout another relationship.
解决这个问题并不像看起来那么简单，您将无法仅通过 1 次递归来解决它.
Solving this isn't as simple as it seems and you won't be able to solve it with just 1 recursion.
以下是我很久以前将所有这些关系组合在一起的解决方案.请记住，对于大型数据集(超过 100k)，计算可能需要一段时间，因为它必须首先识别所有组，最后选择结果.
The following is a solution I made long time ago to group all these relationships together. Keep in mind that, for large datasets (over 100k), it might take a while to calculate, since it has to identify all groups first, and select the result at the end.
CREATE PROCEDURE GetAncestors(@thingID INT)
AS
BEGIN
SET NOCOUNT ON
-- Load your data
IF OBJECT_ID('tempdb..#TreeRelationship') IS NOT NULL
DROP TABLE #TreeRelationship
CREATE TABLE #TreeRelationship (
RelationID INT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED,
Parent INT,
Child INT,
GroupID INT)
INSERT INTO #TreeRelationship (
Parent,
Child)
SELECT
Parent = D.Parent,
Child = D.Child
FROM
Example AS D
UNION -- Data has to be loaded in both ways (direct and reverse) for algorithm to work correctly
SELECT
Parent = D.Child,
Child = D.Parent
FROM
Example AS D
-- Start algorithm
IF OBJECT_ID('tempdb..#FirstWork') IS NOT NULL
DROP TABLE #FirstWork
CREATE TABLE #FirstWork (
Parent INT,
Child INT,
ComponentID INT)
CREATE CLUSTERED INDEX CI_FirstWork ON #FirstWork (Parent, Child)
INSERT INTO #FirstWork (
Parent,
Child,
ComponentID)
SELECT DISTINCT
Parent = T.Parent,
Child = T.Child,
ComponentID = ROW_NUMBER() OVER (ORDER BY T.Parent, T.Child)
FROM
#TreeRelationship AS T
IF OBJECT_ID('tempdb..#SecondWork') IS NOT NULL
DROP TABLE #SecondWork
CREATE TABLE #SecondWork (
Component1 INT,
Component2 INT)
CREATE CLUSTERED INDEX CI_SecondWork ON #SecondWork (Component1)
DECLARE @v_CurrentDepthLevel INT = 0
WHILE @v_CurrentDepthLevel < 100 -- Relationships depth level can be controlled with this value
BEGIN
SET @v_CurrentDepthLevel = @v_CurrentDepthLevel + 1
TRUNCATE TABLE #SecondWork
INSERT INTO #SecondWork (
Component1,
Component2)
SELECT DISTINCT
Component1 = t1.ComponentID,
Component2 = t2.ComponentID
FROM
#FirstWork t1
INNER JOIN #FirstWork t2 on
t1.child = t2.parent OR
t1.parent = t2.parent
WHERE
t1.ComponentID <> t2.ComponentID
IF (SELECT COUNT(*) FROM #SecondWork) = 0
BREAK
UPDATE #FirstWork SET
ComponentID = CASE WHEN items.ComponentID < target THEN items.ComponentID ELSE target END
FROM
#FirstWork items
INNER JOIN (
SELECT
Source = Component1,
Target = MIN(Component2)
FROM
#SecondWork
GROUP BY
Component1
) new_components on new_components.source = ComponentID
UPDATE #FirstWork SET
ComponentID = target
FROM #FirstWork items
INNER JOIN(
SELECT
source = component1,
target = MIN(component2)
FROM
#SecondWork
GROUP BY
component1
) new_components ON new_components.source = ComponentID
END
;WITH Groupings AS
(
SELECT
parent,
child,
group_id = DENSE_RANK() OVER (ORDER BY ComponentID DESC)
FROM
#FirstWork
)
UPDATE FG SET
GroupID = IT.group_id
FROM
#TreeRelationship FG
INNER JOIN Groupings IT ON
IT.parent = FG.parent AND
IT.child = FG.child
-- Select the proper result
;WITH IdentifiedGroup AS
(
SELECT TOP 1
T.GroupID
FROM
#TreeRelationship AS T
WHERE
T.Parent = @thingID
)
SELECT DISTINCT
Result = T.Parent
FROM
#TreeRelationship AS T
INNER JOIN IdentifiedGroup AS I ON T.GroupID = I.GroupID
END
您将看到 @thingID 的值为 100、101、102 和 103 结果是这四个，对于值 200、201 和 202 结果是这三个.
You will see that for @thingID of value 100, 101, 102 and 103 the result are these four, and for values 200, 201 and 202 the results are these three.
我很确定这不是最佳解决方案，但它提供了正确的输出，而且我从来不需要调整它，因为它可以快速满足我的要求.
I'm pretty sure this isn't an optimal solution, but it gives the correct output and I never had the need to tune it up since it works fast for my requirements.
这篇关于如何在 SQL Server 中将层次关系分组在一起的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
