sql - 左 table 上有特殊条件的左连接

不知道这是否可能…我在用sqlite3
架构：
创建表文档（id整数主键，名称字符串）；
创建表revs（id integer主键，doc_id integer，number integer）；
我想选择每一个只有一个修订的工作，一个最高的数字。我怎样才能做到这一点？
现在我正在做一个左连接并获取所有内容，然后在应用程序中过滤它，但是这很糟糕。
（顺便说一句，你能给我推荐一本关于数据库及其工作原理的简单易懂的入门书吗，也许还有一些关于sql的东西……）
谢谢！

最佳答案

不确定您的引擎是否支持此功能，但通常情况下，您会在ansi sql中执行以下操作：

SELECT docs.*
    ,revs.*
FROM docs
INNER /* LEFT works here also if you don't have revs */ JOIN revs
    ON docs.id = revs.doc_id
    AND revs.number IN (
        SELECT MAX(number)
        FROM revs
        WHERE doc_id = docs.id
    )

使用公共表表达式、相关聚合子查询等，有很多方法可以编写等效查询。