我有一张文件表(此处为简化版):
id | rev | content |
---|---|---|
1 | 1 | ... |
2 | 1 | ... |
1 | 2 | ... |
1 | 3 | ... |
如何为每个id选择一行,并且仅选择最大的rev?对于上面的数据,结果应该包含两行:[1,3,…]和[2,1,…]。我使用的是MySQL。
目前,我在while循环中使用检查来检测和重写结果集中的旧版本。但这是实现结果的唯一方法吗?没有SQL解决方案吗?
我有一张文件表(此处为简化版):
id | rev | content |
---|---|---|
1 | 1 | ... |
2 | 1 | ... |
1 | 2 | ... |
1 | 3 | ... |
如何为每个id选择一行,并且仅选择最大的rev?对于上面的数据,结果应该包含两行:[1,3,…]和[2,1,…]。我使用的是MySQL。
目前,我在while循环中使用检查来检测和重写结果集中的旧版本。但这是实现结果的唯一方法吗?没有SQL解决方案吗?
当前回答
注意:在MySQL 8+天中,我可能不会再推荐这种方法了。好几年没用了。
第三种解决方案是MySQL特有的,看起来像这样:
SELECT id, MAX(rev) AS rev
, 0+SUBSTRING_INDEX(GROUP_CONCAT(numeric_content ORDER BY rev DESC), ',', 1) AS numeric_content
FROM t1
GROUP BY id
是的,它看起来很糟糕(转换为字符串和返回等),但根据我的经验,它通常比其他解决方案更快。也许这只是我的用例,但我已经在具有数百万条记录和许多唯一ID的表上使用了它。也许是因为MySQL在优化其他解决方案方面非常糟糕(至少在我提出这个解决方案的5.0天)。
一件重要的事情是GROUP_CONCAT对于它可以建立的字符串有一个最大长度。您可能希望通过设置group_concat_max_len变量来提高此限制。请记住,如果您有大量的行,这将是缩放的限制。
无论如何,如果您的内容字段已经是文本,则上述操作不会直接起作用。在这种情况下,您可能需要使用不同的分隔符,例如\0。您还将更快地达到group_concat_max_len限制。
其他回答
解释
这不是纯SQL。这将使用SQLAlchemy ORM。
我来这里寻求SQLAlchemy帮助,所以我将使用python/SQLAlchemi版本复制Adrian Carneiro的答案,特别是外部连接部分。
此查询回答了以下问题:
“你能把这组记录(基于相同id)中版本号最高的记录还给我吗”。
这允许我复制记录,更新它,增加它的版本号,并以一种可以显示随时间变化的方式复制旧版本。
Code
MyTableAlias = aliased(MyTable)
newest_records = appdb.session.query(MyTable).select_from(join(
MyTable,
MyTableAlias,
onclause=and_(
MyTable.id == MyTableAlias.id,
MyTable.version_int < MyTableAlias.version_int
),
isouter=True
)
).filter(
MyTableAlias.id == None,
).all()
在PostgreSQL数据库上测试。
乍一看。。。
您只需要一个带有MAX聚合函数的GROUP BY子句:
SELECT id, MAX(rev)
FROM YourTable
GROUP BY id
从来没有这么简单,是吗?
我刚刚注意到你也需要内容栏。
这是SQL中一个非常常见的问题:在每个组标识符的列中查找具有某个最大值的行的整个数据。在我的职业生涯中我听过很多。事实上,这是我在当前工作的技术面试中回答的问题之一。
事实上,StackOverflow社区创建了一个标签来处理这样的问题是非常常见的:每个组中最优秀的一个。
基本上,有两种方法可以解决这个问题:
使用简单组标识符连接,组子查询中的最大值
在这种方法中,首先在子查询中找到组标识符,即组中的最大值(上面已经解决)。然后,将表连接到子查询,组标识符和组中的最大值相等:
SELECT a.id, a.rev, a.contents
FROM YourTable a
INNER JOIN (
SELECT id, MAX(rev) rev
FROM YourTable
GROUP BY id
) b ON a.id = b.id AND a.rev = b.rev
左侧使用自身连接,调整连接条件和过滤器
在这种方法中,您可以将表与自身连接起来。组标识符中包含相等项。然后,两个聪明的动作:
第二个连接条件的左侧值小于右侧值执行步骤1时,实际具有最大值的行的右侧将为NULL(这是一个LEFT JOIN,记得吗?)。然后,我们过滤合并结果,只显示右侧为NULL的行。
所以你最终会得到:
SELECT a.*
FROM YourTable a
LEFT OUTER JOIN YourTable b
ON a.id = b.id AND a.rev < b.rev
WHERE b.id IS NULL;
结论
这两种方法带来的结果完全相同。
如果组标识符的组中有两行具有最大值,则两种方法中的结果都是这两行。
这两种方法都与SQLANSI兼容,因此,无论其“味道”如何,都可以与您喜爱的RDBMS一起使用。
这两种方法都是性能友好的,但是您的里程可能会有所不同(RDBMS、DB结构、索引等)。确保你选择对你最有意义的那一个。
按相反顺序对rev字段进行排序,然后按id进行分组,每个分组的第一行是rev值最高的一行。
SELECT * FROM (SELECT * FROM table1 ORDER BY id, rev DESC) X GROUP BY X.id;
测试时间:http://sqlfiddle.com/具有以下数据
CREATE TABLE table1
(`id` int, `rev` int, `content` varchar(11));
INSERT INTO table1
(`id`, `rev`, `content`)
VALUES
(1, 1, 'One-One'),
(1, 2, 'One-Two'),
(2, 1, 'Two-One'),
(2, 2, 'Two-Two'),
(3, 2, 'Three-Two'),
(3, 1, 'Three-One'),
(3, 3, 'Three-Three')
;
这在MySql 5.5和5.6中给出了以下结果
id rev content
1 2 One-Two
2 2 Two-Two
3 3 Three-Two
另一种方法是在OVERPARTITION子句中使用MAX()分析函数
SELECT t.*
FROM
(
SELECT id
,rev
,contents
,MAX(rev) OVER (PARTITION BY id) as max_rev
FROM YourTable
) t
WHERE t.rev = t.max_rev
本文中已经记录的另一个ROW_NUMBER()OVERPARTITION解决方案是
SELECT t.*
FROM
(
SELECT id
,rev
,contents
,ROW_NUMBER() OVER (PARTITION BY id ORDER BY rev DESC) rank
FROM YourTable
) t
WHERE t.rank = 1
此2 SELECT在Oracle 10g上运行良好。
MAX()解决方案的运行速度肯定比ROW_NUMBER()方案快,因为MAX()复杂性为O(n),而ROW_NUMBER()复杂性最低为O(n.log(n)),其中n表示表中的记录数!
我惊讶地发现,没有答案提供SQL窗口函数解决方案:
SELECT a.id, a.rev, a.contents
FROM (SELECT id, rev, contents,
ROW_NUMBER() OVER (PARTITION BY id ORDER BY rev DESC) ranked_order
FROM YourTable) a
WHERE a.ranked_order = 1
在SQL标准ANSI/ISO标准SQL:2003中添加了窗口(或窗口)功能,后来又用ANSI/ISO标准SQL:2008进行了扩展。有更多类型的秩函数可用于处理平局问题:rank、DENSE_rank、PERSENT_rank。