时间戳

首页 > 解决方案 > 给定多个键的组合，在树中找到最佳匹配

sql - 给定多个键的组合，在树中找到最佳匹配

问题描述

我有一个看起来与此类似的结构/树。

CostType 是强制性的，可以单独存在，但它可以有父级 ProfitType 或 Unit 以及其他 CostTypes 作为子级。

只能有重复的单位。其他不能在结构中多次出现。

| ID | name          | parent_id | ProfitType | CostType | Unit |
| -: | ------------- | --------: |
|  1 | Root          |    (NULL) | 
|  2 | 1             |         1 |       300  |          |      |
|  3 | 1-1           |         2 |            |      111 |      |
|  4 | 1-1-1         |         3 |            |          |    8 |
|  5 | 1-2           |         2 |            |      222 |      |
|  6 | 1-2-1         |         5 |            |      333 |      |
|  7 | 1-2-1-1       |         6 |            |          |    8 |
|  8 | 1-2-1-2       |         6 |            |          |    9 |


Parameters           |  should RETURN           |

(300,111,8)          | 4                        |
(null,111,8)         | 4                        |
(null,null,8)        | first match, 4           |
(null,222,8)         | best match, 5            |
(null,333,null)      | 6                        |

我不知道如何创建一个接收 (ProfitType, CostType, Unit) 并从结构中返回最佳匹配 ID 的函数。

标签： sqlsql-server

解决方案

对不起，我没有足够的代表发表评论。

您必须定义“最佳答案”（例如，为什么 null,222,8 的答案不是 7 或 null 而不是 5？），但这是我要使用的方法：

派生一个新表，其中 ProfitType 和 CostType 明确列出，而不是仅通过继承。我会通过使用游标（我知道有多糟糕）并跟随 parent_id 直到找到 ProfitType 和 CostType 来解决这个问题——或者到达根目录。这假定 parent_id 的子/孙级别数量不受限制。如果有限制，那么您可以改为使用 N 个自连接，其中 N 是允许的 parent_id 级别数。

然后对派生表运行多个查询。第一个查询将是完全匹配的（如果找到则退出）。然后下一个查询将针对“最佳”部分匹配（如果找到则退出），然后查询第二佳、第三佳等，直到您用尽“最佳”匹配标准。

如果您需要嵌套的父 CostTypes 作为“最佳匹配”标准的一部分，那么我将在派生表中为具有多个 CostTypes 且具有 CostType“级别”的每一行创建重复条目。级别 1 是实际的 CostType。级别 2 是 CostType 的父级，级别 3 等。然后您的最佳匹配查询将返回多行，您需要选择具有最低级别的行（这是最近的父/祖父）。

推荐阅读