sql - 对字符串的一部分进行分组时避免子查询

Question

我有一份价格不同的产品和组件 list 。这是一个简化的示例。

drop table if exists #group_test
Create Table #group_test
    (
    ID Integer
    , Hierarchy Nvarchar(200)
    , Price Integer
    )

Insert Into #group_test
values 
    (1,'001',10)
    , (2,'001.001',20)
    , (3,'001.002',5)
    , (4,'001.002.001',3)
    , (5,'001.002.002',2)
    , (6,'001.002.003',4)
    , (7,'001.003',6)

<表类="s-表"><头>身份证层次结构价格<正文>1001102001.001203001.00254001.002.00135001.002.00226001.002.00347001.0036

ID 列是产品或组件的 ID。层次结构显示了构建产品所需的内容。例如产品 1 包括:

• 组成部分 2
• 产品 3
• 组成部分 7

产品 3 包括:

• 组成部分 4
• 组成部分 5
• 组成部分 6

现在我想对每个产品或组件及其所有子实体的价格求和。这是我想要的结果:

<表类="s-表"><头>身份证层次结构价格总价<正文>100110502001.00120203001.0025144001.002.001335001.002.002226001.002.003447001.00366

我通过子查询获得了预期的结果。但是因为 table 很大，我觉得这样效率很低。

Select 
ID
, Hierarchy
, Price
, (
    Select sum(Price)
    From #group_test as in_tb
    where in_tb.Hierarchy like Left(out_tb.Hierarchy, 3+4*(Len(out_tb.Hierarchy)-Len(Replace(out_tb.Hierarchy,'.','')))) + '%'
) as Total_Price 
From #group_test as out_tb

我想使用 over(分区依据)但是那没有用:

Select 
ID
, Hierarchy
, Price
, sum(Price) 
    over (partition by Left
            (
                Hierarchy
                , 3+4*
                (
                Len(Hierarchy)-
                Len(Replace(Hierarchy,'.',''))
                )
            )
        )
as Total_Price
From #group_test

有没有一种方法可以使用 over(分区依据)来获得与我的子查询相同的结果？或者您知道其他有效且易于阅读的方法吗？

Answer 1

很抱歉 - 由于您设置数据的方式，对此问题的回答会更加简单。

顺便说一句 - 我假设层次结构中的所有级别只能从 000 到 999 - 没有像 001.11111.002 这样的层次结构 - 如果是这样的话，它会变得有点复杂。

但是，由于每个级别都有其独特的相关价格，您可以简单地添加所有 child (包括 child 的 child 等)的价格以获得总价。 p>

见下文和this db<>fiddle例如代码

SELECT   out_tb.ID,
         out_tb.Hierarchy,
         out_tb.Price,
         SUM(in_tb.Price) AS Total_Price
FROM     #group_test AS out_tb
         INNER JOIN #group_test AS in_tb ON in_tb.hierarchy LIKE out_tb.hierarchy + N'%'
GROUP BY out_tb.ID,
         out_tb.Hierarchy,
         out_tb.Price;