分层 SQL 选择查询

Question

我正在使用MS SqlServer 2008。我有一个表"用户"。此表具有 bigint 的关键字段 ID。还有一个字段 varchar 的父母，它对用户父 ID 的所有链进行编码。例如：

用户表：

ID | Parents
1  | null
2  | ..
3  | ..
4  | 3,2,1

此处用户 1 没有父项，用户 4 具有父项链 3->2->1。我创建了一个函数，该函数解析用户的 Parents 字段并返回用户 ID 为 bigint 的结果表。

现在我需要一个查询，它将选择并加入一些请求用户及其父级的 ID(用户及其父级的顺序并不重要(。我不是SQL专家，所以我能想到的只有以下内容：

WITH CTE AS(
SELECT
    ID,
    Parents
FROM
[Users]
WHERE
(
     [Users].Name = 'John'
)
UNION ALL
SELECT
    [Users].Id,
    [Users].Parents
FROM [Users], CTE
WHERE
(
    [Users].ID in (SELECT * FROM GetUserParents(CTE.ID, CTE.Parents) )
)) 
SELECT * FROM CTE

基本上它有效。但是此查询的性能非常差。我相信在哪里.在。。这里的表达是一个瓶颈。据我了解 - 不仅仅是将 CTE(找到的用户的 ID(的第一个子查询与 GetUserParents(用户父母的 ID(的结果连接起来，它必须枚举 Users 表中的所有用户并检查他们中的每一个是否是函数结果的一部分(并根据执行计划判断 - Sql Server 对结果进行不同的顺序以提高 WHERE 的性能。在。。语句 - 这本身是合乎逻辑的，但一般来说，我的目标不需要。但是这种不同的顺序占用了查询执行时间的 70%(。所以我想知道如何改进这个查询，或者也许有人可以提出另一种方法来解决这个问题？

感谢您的任何帮助！

Answer 1

问题中的递归查询看起来是多余的，因为您已经形成了 GetUserParents 中所需的 ID 列表。也许将其更改为Users SELECT，并与WHERE/JOIN GetUserParents().

select Users.*
from Users join
     (select ParentId
      from (SELECT * FROM Users where Users.Name='John') as U
           cross apply [GetDocumentParents](U.ID, U.Family, U.Parents))
     as gup
on Users.ID = gup.ParentId

由于GetDocumentParents期望标量并且select... where生成一个表，因此我们需要将该函数应用于表的每一行(即使我们"知道"只有一个(。这就是apply所做的。

我使用缩进来强调查询的概念部分。 (select...) as gup是Users join的实体; (select...) as U cross apply fn()是FROM的论据。

理解此查询的关键知识是了解cross apply的工作原理：

• 它是FROM子句的一部分(出乎意料;所以语法在 FROM (Transact-SQL((
它
• 转换了它左侧的表表达式，结果成为FROM的参数(我用缩进强调了这一点(

转换为：对于每一行，它

• 使用此行运行其右侧的表表达式(在本例中为表值函数的调用(
将行中的列添加到
• 结果集中，后跟调用中的列。(在我们的例子中，从函数返回的表有一个名为 ParentId 的列(
  • 因此，如果调用返回多行，则添加的记录将是表中的同一行，该表附加了函数中的每一行。

这是一个cross apply因此只有在函数返回任何内容时才添加行。如果这是另一种风格，outer apply，无论如何都会添加一行，如果函数的列不返回任何内容，则在函数的列中添加一个NULL。

Answer 2

这个"解析"的东西甚至违反了 1NF。使Parents字段仅包含直接父级(最好是外键(，然后可以使用递归查询检索整个子树。