如果TOP (1) WITH TIES
从内部查询中删除并设置外部查询以返回前 k 行,会发生什么情况?
我也想知道这项修正案是否有帮助。它应该比使用TOP
以下命令更有效:
DECLARE @start FLOAT = 1000
,@k INT = 20
,@p FLOAT = 2;
WITH NearestPoints AS
(
SELECT *
,T.g.STDistance(@x) AS dist
,ROW_NUMBER() OVER (ORDER BY T.g.STDistance(@x)) AS rn
FROM Numbers
JOIN T WITH(INDEX(spatial_index))
ON T.g.STDistance(@x) < @start*POWER(@p,Numbers.n)
AND (Numbers.n - 1 = 0
OR T.g.STDistance(@x) >= @start*POWER(@p,Numbers.n - 1)
)
)
SELECT *
FROM NearestPoints
WHERE rn <= @k;
注意:未经测试-我无法在此处访问sql 2008。