使用MAX修订版对记录进行分组的最佳方式

Question

我有一个这样的源表：

表a:

批准

id	修订	状态
1	0
1	1	待定

似乎可以通过一些(条件(聚合来实现这一点：

SELECT id,
MAX(revision) AS latest_rev,
MAX(CASE status WHEN 'APPROVED' THEN revision END) AS latest_approved_rev
FROM (VALUES(1,0,'APPROVED'),
(1,1,'PENDING'))V(id,revision,status)
GROUP BY id;

您有一个正确的查询(如果我理解您的要求(。您非常接近理想的查询。但是您的外部WHERE子句包含一个相关的(依赖的(子查询，并且这些子查询并不总是执行良好。

您可以将其视为两个子查询的JOIN。你拥有的那个。

SELECT id,
MAX(revision) AS revision
FROM table_a
WHERE status = 'APPROVED'
GROUP BY id

还有这个。

SELECT id,
MAX(revision) AS revision
FROM table_a
GROUP BY id

你完全加入他们的行列。就像这样。

SELECT max.id, 
latest.revision as latest_rev,
approved.revision as approved_rev
FROM (
SELECT id,
MAX(revision) AS revision
FROM table_a
GROUP BY id
) latest
FULL JOIN (
SELECT id,
MAX(revision) AS revision
FROM table_a
GROUP BY id
) approved on latest.id = approved.id

在这种情况下，您实际上可以使用LEFT JOIN，因为您知道approved子查询中的每个id也存在于latest子查询中。