棘手的逻辑问题 - 如何为此查询创建基于集合的解决方案？

Question

使用 SQL Server 2008 和 TSQL 的任何可用功能，我试图弄清楚如何解决以下问题是否存在不涉及临时表的基于集合的解决方案：

给定一组具有父子关系的节点，以及一组适用于每个的键值对，并且考虑到 节点层次结构较深层的值（对于给定的键值对）将覆盖具有相同键的值 是从祖先节点继承的，选择：

1. 适用于给定节点的完整键值
2. 对集 该节点的继承值集

模式如下：

create table Node
(
    ID bigint identity primary key,
    ParentID bigint null foreign key references Node(ID),
    Name nvarchar(100)
);

create table KeyValuePair
(
    ID bigint identity primary key,
    KeyName nvarchar(100) not null,
    Value nvarchar(1000) not null,
    NodeID bigint not null foreign key references Node(ID),

    unique (KeyName, NodeID)
);

结果集本质上包括列 KeyName< /code>、值、InheritedValue。

我一直在尝试使用公用表表达式来做到这一点，但它的逻辑有点棘手。

Answer 1

我根据问题设置了 Node 和 KeyValuePair 表，并填充了一些示例值，这样我的层次结构如下：

Root
|---A
|   |---A1
|   |---A2
|
|---B
    |---B1
   |---B2

我分配了两个属性，名为“Property 1”和“Property 2”，每个属性在根中定义，分别具有值“Root Prop 1”和“Root Prop 2” 。在 A 中，我用值“A Prop 1”覆盖“属性 1”，在 B 中，我用值“B Prop 2”覆盖“属性 2”。

set identity_insert Node on
insert into Node(ID,ParentID,Name)
values (1,null,'Root'),(2,1,'A'),(3,1,'B'),(4,2,'A1'),(5,2,'A2'),
       (6,3,'B1'),(7,3,'B2')
set identity_insert Node off

insert into KeyValuePair(KeyName, [Value], NodeID)
values ('Property 1','Root Prop 1',1),
('Property 2','Root Prop 2',1),
('Property 1','A Prop 1',2),
('Property 2','B Prop 2',3)

调用节点 A1 的 Nathan 解决方案不会产生任何行！

Nathan 解决方案中的 where 子句应该是键和 v 之间连接的条件，导致修改后的过程如下所示（我还将 DataValue 重命名为 KeyValuePair 以与原始问题一致）：

create procedure dbo.ListDataValues
    @nodeid bigint
as
begin
    with nodes as (
        select ID, ParentID, 0 as Level
        from Node n where ID=@nodeid
        union all
        select n.ID, n.ParentID, c.Level+1 as Level
        from Node n inner join nodes c on c.ParentID = n.ID
    ),
    keys as (
        select distinct(KeyName)
        from KeyValuePair
        where NodeID in (select ID from nodes)
    )
    select
        keys.KeyName,
        v.Value,
        i.Value as [InheritedValue],
        i.NodeID as [InheritedFromNodeID]
    from
        keys
        left join KeyValuePair v on v.KeyName = keys.KeyName
                                     and v.NodeID = @nodeid
        left join KeyValuePair i on i.KeyName = keys.KeyName
            and i.NodeID = (select top 1 NodeID from KeyValuePair d
                            inner join nodes k on k.ID = d.NodeID
                            where Level > 0 and d.KeyName = i.KeyName
                            order by [Level])
end
go

这产生了预期的正确结果:

KeyName      Value   InheritedValue    InheritedFromNodeID
------------ ------- ----------------- --------------------
Property 1   NULL    A Prop 1          2
Property 2   NULL    Root Prop 2       1

Answer 2

您应该考虑使用嵌套集模型来存储层次结构。这是描述它的链接：
http://mikehillyer.com/articles/managing-hierarchical-data-in -mysql/

它支持一种更加 SQL 友好的方法来检索有关分层信息的公共信息。

然后，您可以通过 Node 上的查询和 KeyValuePair 的单个连接来满足您的要求。

Answer 3

好吧，我自己解决了。可能还有其他方法可以做到这一点，但这似乎效果很好：

create procedure dbo.ListDataValues
    @nodeid bigint
as
begin
    with nodes as (
        select ID, ParentID, 0 as Level
        from Node n where ID=@nodeid
        union all
        select n.ID, n.ParentID, c.Level+1 as Level
        from Node n inner join nodes c on c.ParentID = n.ID
    ),
    keys as (
        select distinct(KeyName)
        from DataValue
        where NodeID in (select ID from nodes)
    )
    select v.KeyName, v.Value, i.Value as [InheritedValue], i.NodeID as [InheritedFromNodeID]
    from keys
    left join DataValue v on v.KeyName = keys.KeyName
    left join DataValue i on i.KeyName = keys.KeyName
        and i.NodeID = (select top 1 NodeID from DataValue d
                        inner join nodes k on k.ID = d.NodeID
                        where Level > 0 and d.KeyName = i.KeyName
                        order by Level)
    where v.NodeID = @nodeid
end

Answer 4

这是物化路径大放异彩的案例之一。以下是我如何使用它解决类似问题：

将您的配置设置作为层次结构存储在数据库中。