右表上有特殊条件的左连接

Question

不知道这是否可能..我正在使用 sqlite3 架构：

CREATE TABLE 文档（id 整数主键，名称字符串）；
CREATE TABLE revs（id整数主键，doc_id整数，number整数）；

我想选择仅包含其中一个修订版（编号最高的修订版）的每一项作业。我怎样才能实现这个目标？ 现在我正在做左连接并获取所有内容，然后我在应用程序中过滤它，但这很糟糕..

（顺便说一句，你能给我推荐一本关于数据库的好又简单的入门书以及如何它们可以工作，也许还有一些关于 sql 的东西..） 谢谢！

Answer 1

试试这个

   Select * From docs d
      Join revs r
         On r.doc_id = d.id
   Where r.number = 
         (Select Max(number ) from revs
          Where Doc_Id = d.Id)

，或者，如果你想要没有修订的文档（这可能吗？）

   Select * From docs d
      Left Join revs r
         On r.doc_id = d.id
           And r.number = 
                (Select Max(number ) from revs
                 Where Doc_Id = d.Id)

Answer 2

不确定您的引擎是否支持此功能，但通常，您会在 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
    )

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

Answer 3

select d.*, r.max_number
from docs d
left outer join (
    select doc_id, max(number) as max_number
    from revs
    group by doc_id
) r on d.id = r.doc_id

Answer 4

数据库设计：为凡人设计数据库，作者：Hernandez

SQL：实用 SQL 手册

如果您想伤脑筋，可以阅读 乔·塞尔科。

Answer 5

这是一个非常好的数据库设计书籍列表

https://stackoverflow.com/search?q=database+book

Answer 6

如果每个作业都有修订（例如，从版本 0 开始），我将使用与 OrbMan 相同的方法，但使用内部联接。 （如果您确定正在寻找一对一的匹配。为什么不让 SQL 也知道呢？）

select d.*, r.max_number
from docs d
inner join
(
    select doc_id, max(number) as max_number
    from revs
    group by doc_id
) r on d.id = r.doc_id

Answer 7

我推荐“数据库设计的合理方法”作为良好设计实践的优秀介绍。 （我有点偏见。我写的。但是，嘿，到目前为止，它在亚马逊上的平均评论为 5 星，这些评论都不是我或任何朋友或亲戚贡献的。）