级联函数SQL从树结构到平面数据结构

Question

我有两个表：

表1 t_SearchCriteria:

------------------------------
ID   |  VALUE     | IDParent   |
-----|------------|------------|
0   |   root     |     -1     |
-----|------------|------------|
1   | JAMES      |      0     |
-----|------------|------------|
2   | ISAC       |      0     |
-----|------------|------------|
3   | LISA       |      1     |
-----|------------|------------|
4   | Andrew     |      3     |
-----|------------|------------|
5   | LISA       |      2     |
-----|------------|------------|
6   | EZREAL     |      5     |
-----|------------|------------|
10  | twitch     |      2     |
-----|------------|------------|
13  | LUX        |      0     |
-----|------------|------------|
14  | LISA       |      13    |
-----|------------|------------|
15  | EZREAL     |      14    |
-----|------------|------------|

编辑：这是树的表示：

_______root_______
/        |         
JAMES       ISAC       LUX
|        /            |
LISA   TWITCH  LISA    LISA
|              |       |
Andrew        EZREAL   EZREAL

我的第二张表如下：

表t_路径

idPath|Person1|  Son  |grandSon|grandgrandSon|
------|-------|-------|--------|-------------|
1  |root   |JAMES  | LISA   |ANDREW       |
------|-------|-------|--------|-------------|
2  |root   |ISAC   | LISA   |EZREAL       |
------|-------|-------|--------|-------------|
3  |root   |ISAC   | NULL   |TWITCH       |
------|-------|-------|--------|-------------|
4  |root   |LUX    | NULL   |  NULL       |
------|-------|-------|--------|-------------|
5  |root   |LUX    | LISA   |  NULL       |
------|-------|-------|--------|-------------|
6  |root   |LUX    | LISA   | EZREAL      |
------|-------|-------|--------|-------------|

我的需要是找出一种从表2(t_Path)开始的方法(函数或过程)，并在t_searchCriteria表中找到每个叶(如果不是null，则为grandson的值，否则为sunder的值，如果不是null等)id：

由于我们可以在t_search条件表中具有相同的节点值，因此节点的唯一性是它的值、它的父值和它的grandParentValue(我们有另一个规则；一个父不能有两个同名的子节点)

我试过制作一个函数，但除了使用c#或其他编程语言之类的对象外，我没有找到在另一个函数中制作函数的方法。

我需要制作一个函数，它取一个int ID，这是表t_path中路径的ID，并计算出路径的叶(已经完成)，这里的问题是如何从t_searchCriteria表中获取该叶的ID，因为我们可以有多个具有相同值(名称)的条件，即使具有相同的父名称，grandPrent值也会有所不同。

例如执行：Select FunctionGetCriteriaId(6)将返回15

其中6是路径的id：6 |root |LUX | LISA | EZREAL |

15是标准的id：15 | EZREAL | 14 |

有人能帮我弄清楚吗？

EDIT：更具体地说，函数采用表2中路径的id，例如5(5|root|LUX|LISA|NULL|)，并返回"的id；LISA"；(叶子不是其他的；))在表1中是14。(当然要注意之前制定的规则。)

编辑2:树中更新的唯一性条件

Answer 1

使用LEFT JOINS、MAX和COALESCE可以轻松完成此操作。这是一个完整的工作示例。你可以玩它：

DECLARE @t_SearchCriteria TABLE
(
[ID] SMALLINT
,[Value] VARCHAR(12)
,[IDParent] SMALLINT
);
INSERT INTO @t_SearchCriteria ([ID], [Value], [IDParent])
VALUES (0, 'root', -1)
,(1, 'JAMES', 0)
,(2, 'ISAC', 0)
,(3, 'LISA', 1)
,(4, 'Andrew', 3)
,(5, 'LISA', 2)
,(6, 'EZREAL', 5)
,(10, 'twitch', 2)
,(13, 'LUX',  0)
,(14, 'LISA', 13)
,(15, 'EZREAL', 14);
DECLARE @t_Path TABLE
(
[idPath] SMALLINT
,[Person1] VARCHAR(12)
,[Son] VARCHAR(12)
,[grandSon] VARCHAR(12)
,[grandgrandSon] VARCHAR(12)
);
INSERT INTO @t_Path ([idPath], [Person1], [Son], [grandSon], [grandgrandSon])
VALUES (1, 'root', 'JAMES', 'LISA', 'ANDREW')
,(2, 'root', 'ISAC', 'LISA', 'EZREAL')
,(3, 'root', 'ISAC',  'TWITCH', NULL)
,(4, 'root', 'LUX', NULL, NULL)
,(5, 'root', 'LUX', 'LISA', NULL)
,(6, 'root', 'LUX', 'LISA', 'EZREAL');

-- the function input parameter
DECLARE @idPath SMALLINT = 5;
-- the function body
DECLARE @Person1 VARCHAR(12)
,@Son VARCHAR(12)
,@grandSon VARCHAR(12)
,@grandgrandSon VARCHAR(12);
SELECT @Person1 = [Person1]
,@Son = [Son]
,@grandSon = [grandSon]
,@grandgrandSon = [grandgrandSon]
FROM @t_Path P
WHERE P.[idPath] = @idPath;
SELECT COALESCE(MAX(S5.[ID]), MAX(S4.[ID]), MAX(S3.[ID]), MAX(S2.[ID]), MAX(S1.[ID]))
FROM @t_SearchCriteria S1
LEFT JOIN @t_SearchCriteria S2
ON S1.[ID] = S2.[IDParent]
AND S1.[Value] = @Person1
LEFT JOIN @t_SearchCriteria S3
ON S2.[ID] = S3.[IDParent]
AND S2.[Value] = @Son
AND @Person1 IS NOT NULL
LEFT JOIN @t_SearchCriteria S4
ON S3.[ID] = S4.[IDParent]  
AND S3.[Value] = @grandSon
AND @grandgrandSon IS NOT NULL
LEFT JOIN @t_SearchCriteria S5
ON S4.[ID] = S5.[IDParent]
AND S4.[Value] = @grandgrandSon
WHERE S1.[Value] = @Person1;