sqlalchemy - 分组项目并遍历子列表

Question

想象一下有这样一个表格：

| Name       | Version | Other |
| ---------------------|-------|
| Foo        | 1       | 'a'   |
| Foo        | 2       | 'b'   |
| Bar        | 5       | 'c'   |
| Baz        | 3       | 'd'   |
| Baz        | 4       | 'e'   |
| Baz        | 5       | 'f'   |
--------------------------------

我想写一个sqlalchemy的查询语句，来列出所有的项目（作为映射对象，而不仅仅是'名称'这一列），并且这些项目的版本是最大的：Foo-2-b, Bar-5-c, Baz-5-f。我知道我需要使用group_by方法，但除此之外，我对如何获取子列表（然后找到最大的元素）感到困惑。SQLAlchemy的文档似乎对此并没有很清楚的说明。

在实际情况中，还有很多其他的列（比如'其他'），这就是为什么我需要返回实际的行对象（映射类），而不仅仅是'名称'的值。

Answer 1

这里是你可以用 engine.execute 命令来调用的 SQL 语句：

select
    t1.*
from
    table t1
    inner join 
        (select
            Name,
            max(version) as Version
         from
            table
         group by
            name) s on
        s.name = t1.name
        and s.version = t1.version

Answer 2

如果你需要完整的对象，你需要在一个子查询中按名称选择最大版本，然后将其连接到主查询：

max_versions = session.query(Cls.name, func.max(Cls.version).label('max_version'))\
                      .group_by(Cls.name).subquery()
objs = session.query(Cls).join((max_versions,
           and_(Cls.name == max_versions.c.name,
                Cls.version == max_versions.c.max_version)
       )).all()

这样会得到类似下面的结果：

SELECT tbl.id AS tbl_id, tbl.name AS tbl_name, tbl.version AS tbl_version
FROM tbl JOIN (SELECT tbl.name AS name, max(tbl.version) AS max_version
FROM tbl GROUP BY tbl.name) AS anon_1 ON tbl.name = anon_1.name AND tbl.version = anon_1.max_version

要注意的是，如果有多个最大版本的行，你会得到多个相同名称的行。