SQL以自定义排序顺序加入第一条记录
有时我想加入一条记录,我可以轻松识别为“按此排序并选择第一条记录”。我想知道这里的最佳做法。SQL以自定义排序顺序加入第一条记录
例如在我的数据模型中我有服务和实现。服务可能有多个实现。实现有版本号;可以安装零个或多个实现,并且还可以启用其中的一个。现在我想使用以下规则为每个服务选择“当前实现”:
- 如果没有安装任何内容,那么当前的实现是最近的一个。
- 如果安装了多个安装,那么当前是启用的或者如果没有启用,则再次是最新的。
一些例子:
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使用的内部记录标识。其次,在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的特定:)
您可以使用NOT EXISTS条件排除除期望匹配以外的所有条件。加入与[service]列匹配的[service]和[implementation]的行,只要没有[[implementation]]的另一行也与[service]列相匹配,但是是首选的,因为它会在所需的顺序。这是主意。
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语句未发布。)
谢谢,史蒂夫;这看起来非常有前途:) –
+1,似乎绝对比我的解决方案中的一群GROUP BY更好。 –
我想不出什么比这更好的:
SELECT
i.Service,
i.Installed,
i.Enabled,
MAX(i.Version) AS Version
FROM implementation i
INNER JOIN (
SELECT
i.Service,
i.Installed,
MAX(i.Enabled) AS Enabled
FROM implementation i
INNER JOIN (
SELECT
Service,
MAX(Installed) AS Installed
FROM implementation
GROUP BY
Service
) m ON i.Service = m.Service
GROUP BY
i.Service,
i.Installed
) m ON i.Service = m.Service AND i.Installed = m.Installed
GROUP BY
i.Service,
i.Installed
i.Enabled
基本上这个脚本发现的Installed
最大值对于每个Service
,然后将获得的列表加回到原始表格(以过滤所找到的值)并找到最大值Enabled
,然后再次将获取的表格加回到implementation
,最终检索过滤行的最大值Version
。
我想我明白了,但我必须承认这实际上很难写出如此错综复杂的声明:) –
是否有某些原因您不使用INNER JOIN? –
你确定这可以吗?因为子查询可能会返回多于一行,并且您在rowid上使用了相等运算符。 – NullUserException
我可以,但我不明白它会在这里有什么不同。一个内部联接会给我所有匹配的实现,这可以作为一个起点,但是我想选择一个以任何排序顺序排在第一位的实现:) –