使用SQL查询获取父级数据的所有子级。

Question

我正在学习如何查询分层数据以返回父级的所有子级(给出了父级电子邮件)。以下是我的疑问：

WITH emp AS (
  SELECT
    PersonnelNumber,
    Email,
    ReportsToPersonnelNbr,
    1 AS level
  FROM tbl
  WHERE Email = 'XYZ'
 
  UNION ALL
   
  SELECT
    e.PersonnelNumber,
    e.Email,
    e.ReportsToPersonnelNbr,
    emp.level + 1
  FROM tbl e INNER JOIN emp
  ON e.ReportsToPersonnelNbr = emp.PersonnelNumber
)
SELECT e.PersonnelNumber, e.Email, e.ReportsToPersonnelNbr, e.level
FROM emp e
ORDER BY e.level;

我得到了预期的结果，但是返回结果需要更长的时间。例如，2分钟内返回~300行。我该怎么解决这个问题？

更新:我运行了以下sql查询：

CREATE INDEX [IDX_ReportsToPersonnelNbr] ON tbl(ReportsToPersonnelNbr)

然而，我仍然看到，返回结果需要更长的时间。

Answer 1

你需要更好地索引你的桌子。考虑一下服务器将如何找到所需的行，并记住索引需要覆盖所有必要的列。

要使查询运行良好，需要使用以下两个索引

CREATE NONCLUSTERED INDEX [IDX_ReportsToPersonnelNbr] ON tbl
  (Email) INCLUDE (PersonnelNumber, ReportsToPersonnelNbr)
  WITH (DROP_EXISTING = ON);

CREATE NONCLUSTERED INDEX [IDX2_ReportsToPersonnelNbr] ON tbl
  (ReportsToPersonnelNbr) INCLUDE (PersonnelNumber, Email)
  WITH (DROP_EXISTING = ON);