sql - SQL以自定义排序顺序连接第一条记录

Question

有时我想加入一条记录，我可以轻松地将其识别为“按此排序并选择第一条记录”。我不知道这里的最佳做法。

例如，在我的数据模型中，我有服务和实现。一个服务可能有多个实现。实现有版本号。可以安装零个或多个实现，也可以启用其中之一。现在，我要使用以下规则为每个服务选择“当前实现”：


如果未安装任何内容，则当前的实现是最新的实现。
如果安装了多个，则当前是启用的一个，或者如果未启用，则是最新的。


一些例子：

Service  Version  Installed  Enabled
=======  =======  =========  =======
A        1        False      False
A        2        False      False   <- current for service A because most recent

B        1        True       False   <- current for service B because installed
B        2        False      False

C        1        True       False
C        2        True       False   <- current for service C because most recent
C        3        False      False      among installed

D        1        True       True    <- current for service D because enabled
D        2        True       False

我认为，如果我按这些字段排序并选择第一个记录，它将可以解决问题。即我做这样的事情：

SELECT s.service, <other service fields>, i.version, <other impl. fields>
  FROM service s, implementation i
 WHERE i.rowid == (
       SELECT rowid 
         FROM implementation o
        WHERE o.service == s.service
     ORDER BY installed DESC, enabled DESC, version ASC)

更新。我猜这是SQLite特有的。首先， rowid是SQLite使用的内部记录ID。其次，在SQLite中，此上下文中的 SELECT表达式返回单个值。我猜可以将其重写为更通用的SQL，如下所示：

  ...
  FROM service s, implementation i
 WHERE i.service == s.service
   AND i.version == (
       SELECT version 
         FROM ...
     ORDER BY ...
        LIMIT 1)

它在这里有效，但有时没有 rowid或另一个可以用作标识符的字段。还有其他方法可以得到相同的结果吗？也许更通用一些？ （对我来说，更具体的事情也可以，只要它是特定于SQLite的即可：）

Answer 1

您可以使用NOT EXISTS条件排除期望的匹配以外的所有匹配。只要[service]列上匹配的[service]和[implementation]行联接在一起，只要不存在[service]列上也匹配的[implementation]的另一行，但由于它在所需的顺序。这是主意。

SELECT s.service, <other service fields>, i.version, <other impl. fields>
FROM service s JOIN implementation i
ON i.service = s.service
AND NOT EXISTS ( -- where there's no "better" row from i to use
SELECT * FROM implementation AS i2
   WHERE i2.service = i.service
   AND (
     i2.installed > i.installed
     OR (i2.installed = i.installed AND i2.enabled > i.enabled)
     OR (i2.installed = i.installed AND i2.enabled = i.enabled AND i2.version < i.version)
   )
)

使用Microsoft SQL Server或其他支持CROSS APPLY运算符的SQL方言，这要简单得多：

SELECT s.service, <other service fields>, i.version, <other impl. fields>
FROM service s
CROSS APPLY (
  SELECT TOP 1 * FROM implementation
  WHERE implementation.service = s.service
  ORDER BY installed DESC, enabled DESC, version ASC
) AS i

（因为没有发布CREATE TABLE和INSERT语句，所以两种解决方案都未经示例数据测试。）

Steve Kass