如何在 SQL Server 中编写查询以查找最接近的值

假设列被索引，以下应该是相当有效的。

两次查找 10 行，然后返回（最多）20 行。

WITH CTE
     AS ((SELECT TOP 10 *
          FROM   YourTable
          WHERE  YourCol > 32
          ORDER  BY YourCol ASC)
         UNION ALL
         (SELECT TOP 10 *
          FROM   YourTable
          WHERE  YourCol <= 32
          ORDER  BY YourCol DESC))
SELECT TOP 10 *
FROM   CTE
ORDER  BY ABS(YourCol - 32) ASC 

（即可能类似于以下内容）

或另一种可能性（将排序的行数减少到最大 10）

WITH A
     AS (SELECT TOP 10 *,
                       YourCol - 32 AS Diff
         FROM   YourTable
         WHERE  YourCol > 32
         ORDER  BY Diff ASC, YourCol ASC),
     B
     AS (SELECT TOP 10 *,
                       32 - YourCol AS Diff
         FROM   YourTable
         WHERE  YourCol <= 32
         ORDER  BY YourCol DESC),
     AB
     AS (SELECT *
         FROM   A
         UNION ALL
         SELECT *
         FROM   B)
SELECT TOP 10 *
FROM   AB
ORDER  BY Diff ASC

注意：上面的执行计划是针对简单的表定义

CREATE TABLE [dbo].[YourTable](
    [YourCol] [int] NOT NULL CONSTRAINT [SomeIndex] PRIMARY KEY CLUSTERED 
)

从技术上讲，也不应该需要底部分支上的排序，因为它也是由 Diff 排序的，并且可以合并两个排序的结果。但我没能得到那个计划。

查询有ORDER BY Diff ASC, YourCol ASC，而不仅仅是ORDER BY YourCol ASC，因为这就是最终摆脱计划顶部分支中的排序的原因。我需要在其中添加辅助列（即使它永远不会改变结果，因为YourCol对于具有相同 Diff 的所有值都是相同的），因此它将通过合并连接（连接）而不添加排序。

SQL Server 似乎能够推断在 X 上按升序搜索的索引将提供按 X + Y 排序的行，并且不需要排序。但是它不能推断出以降序遍历索引会以与 YX 相同的顺序传递行（或者甚至只是一元减 X）。计划的两个分支都使用索引来避免排序，但TOP 10底部分支中的然后按Diff（即使它们已经按该顺序）排序，以使它们按所需的顺序进行合并。

对于其他查询/表定义，仅使用一种分支来获取合并计划可能会更棘手或不可能 - 因为它依赖于查找 SQL Server 的排序表达式：

1. 接受索引查找将提供指定的顺序，因此在顶部之前不需要排序。
2. 很高兴在合并操作中使用，因此不需要在TOP

我有点困惑和惊讶，在这种情况下我们必须做 Union。以下更简单更高效

SELECT TOP (@top) *
FROM @YourTable
ORDER BY ABS(YourCol-@x)

以下是比较两个查询的完整代码和执行计划

DECLARE @YourTable TABLE (YourCol INT)
INSERT @YourTable (YourCol)
VALUES  (32),(11),(15),(123),(55),(54),(23),(43),(44),(44),(56),(23)

DECLARE @x INT = 100, @top INT = 5

--SELECT TOP 100 * FROM @YourTable
SELECT TOP (@top) *
FROM @YourTable
ORDER BY ABS(YourCol-@x)

;WITH CTE
     AS ((SELECT TOP 10 *
          FROM   @YourTable
          WHERE  YourCol > 32
          ORDER  BY YourCol ASC)
         UNION ALL
         (SELECT TOP 10 *
          FROM   @YourTable
          WHERE  YourCol <= 32
          ORDER  BY YourCol DESC))
SELECT TOP 10 *
FROM   CTE
ORDER  BY ABS(YourCol - 32) ASC 

Martin 的第二个建议的细化：

WITH AB
     AS (SELECT *, ABS(32 - YourCol) AS Offset
         FROM   YourTable),
SELECT TOP 10 *
FROM   AB
ORDER  BY Offset ASC