我用下面的方法自己解决了一个问题。我首先创建了一个临时表,并插入了每个唯一id的最大rev值。
CREATE TABLE #temp1
(
id varchar(20)
, rev int
)
INSERT INTO #temp1
SELECT a.id, MAX(a.rev) as rev
FROM
(
SELECT id, content, SUM(rev) as rev
FROM YourTable
GROUP BY id, content
) as a
GROUP BY a.id
ORDER BY a.id
然后我将这些最大值(temp1)加入到所有可能的id/内容组合中。通过这样做,我自然过滤掉了非最大id/内容组合,只剩下每个组合的最大rev值。
SELECT a.id, a.rev, content
FROM #temp1 as a
LEFT JOIN
(
SELECT id, content, SUM(rev) as rev
FROM YourTable
GROUP BY id, content
) as b on a.id = b.id and a.rev = b.rev
GROUP BY a.id, a.rev, b.content
ORDER BY a.id