sql - 避免重复引用多个连接表的子查询

Question

我有一个子查询 (LastActivityOn)，我想在三个地方使用它:我的投影(SELECTed 输出)、ORDER BY 和 WHERE 子句。

SELECT TOP 175
  (SELECT MAX(ActivityDate) FROM (VALUES
    (UserRegistration.CreatedOn),
    (UserRegistration.ActivatedOn),
    (UserRegistration.LastLoginOn),
    (UserRegistration.UpdatedOn),
    (UserProfile.LastPostedOn)) AS AllDates(ActivityDate)) LastActivityOn,
  UserRegistration.FirstName,
  UserRegistration.LastName,
  [15 more columns of various calculated distances, coalesces, etc...]
FROM
  UserRegistration
  INNER JOIN UserProfile ON UserRegistration.Id = UserProfile.RegistrationId
  INNER JOIN (
    SELECT PostalCode, GeoCenter, PrimaryCity, StateOrProvince
    FROM PostalCodes 
    WHERE @OriginPostalCode IS NULL OR PostalCodes.GeoCenter.STDistance(@OriginPoint) < @WithinMeters
  ) AS ProximalPostalCodes ON ProximalPostalCodes.PostalCode = UserRegistration.PostalCode
  [7 more joins including full-text queries]
WHERE
  LastActivityOn > @OldestUserToSearch AND
  [20 more lines of filtering logic]
ORDER BY
  LOG(DATEDIFF(WEEK, LastActivityOn, @Today))/LOG(2),
  FullTextRelevance

请注意 LastActivityOn 出现了 3 次。另请注意，LastActivityOn 子查询引用两个表。我想因为它依赖于父查询中的 join 子句，所以它本质上是一个相关子查询？

当我仅通过用户定义函数获取两个日期的最大值时，我能够在 WHERE 和 ORDER BY 中使用结果值。现在我不能了。

似乎我有几个选择...我可以将整个事情包装在另一个查询中，仅使用添加的事件重复投影。看来我可以以同样的方式使用“WITH”(CTE)。

但是因为我不清楚何时可以或不能按照我想要的方式使用子查询的规则，所以我很容易会错过一些东西。有什么想法吗？

也许 SQL SERVER 足够聪明，只对每个输出行执行一次计算，而我不应该担心它？

编辑:当前正在运行 SQL Server 2008 Standard，但将在某个时候进行升级。另外，RE:日志函数 - 我正在努力将 与 相关性结合起来作为加权总数，所以这是一项正在进行的工作。我将使用 INT 对其进行修剪以用作排名类型，或者通过线性调整将其添加到相关性中。

更正:我能够在 ORDER BY 中使用子查询别名，但不能在任何其他计算中或在 where 子句中使用。感谢 ypercube 指出了这一点。

Answer 1

我不会尝试修改您的查询，但可能是common table expression就是你所需要的。