sql-server - SQL 自联接发现层次结构

Question

考虑一个 SQL Server 2017 表（我们称之为产品），它具有基于主键 (id) 排序的隐含层次结构，具有以下逻辑结构：

Product (root)
 - SKU  (optional children)
 - Rule (optional children)

示例表可能如下所示：

    ID  Item_Type
    1   Product
    2     SKU
    3     SKU
    4     SKU
    5     Rule
    6     Rule
    7   Product
    8     Rule
    9     Rule
    10  Product
    11    SKU

鉴于我想查找每个 SKU 和规则的父产品，什么是合适的查询？结果应该是这样的：

ID  Item_Type ProductId
2     SKU     1
3     SKU     1
4     SKU     1
5     Rule    1
6     Rule    1
8     Rule    7
9     Rule    7
11    SKU     10

标签： sql-serverjoingroupinghierarchy

Answer 1

标量函数也很方便

CREATE FUNCTION FindParentKey
(
    @CurrentItemKey int
)
RETURNS int
AS
BEGIN
    return (
        SELECT MAX(ID)
        FROM Product
        WHERE ID < @CurrentItemKey and Item_Type = 'Product'
    )
END
GO

SELECT ID, Item_Type, dbo.FindParentKey(ID)
FROM Product
WHERE Item_Type <> 'Product'