在单个SQL语句中，SQL JOIN具有两个表并具有一个表的条件

I have an sqlite database which has a table of documents (journal articles), where each document has a unique id. All the authors are listed in another table, with corresponding document id's, and with unique author id's as well. The problem is that articles have different number of authors. If there is one author, I should retrieve just his name. If there are two, I should retrieve both names. If there are more than two, I need to retrieve only the first author and append "et al." to it.

我有一个sqlite数据库,它有一个文档表(期刊文章),其中每个文档都有一个唯一的ID。所有作者都列在另一个表中,具有相应的文档ID,并且还具有唯一的作者ID。问题是文章的作者数量不同。如果有一位作者,我应该只检索他的名字。如果有两个,我应该检索这两个名字。如果有两个以上,我只需要检索第一个作者并附加“等”。它。

In the following example, I need to produce records of the type "Authors - Title". For Document Title1 I shoud have "Name101 et al. - Title1", for Title2 - "Name201 - Title2", for Title3 - "Name301 & Name302 - Title3".

在以下示例中,我需要生成“作者 - 标题”类型的记录。对于文件标题1,我对标题2 - “名称201 - 标题2”,标题3 - “名称301和名称302-标题3”具有“Name101等人 - 标题1”。

DocsTable
Title   |  DocId
Title1  |  10
Title2  |  20
Title3  |  30

AuthorsTable
Name      | AuthorId  | DocId
Name101   |    101    | 10
Name102   |    102    | 10
Name103   |    103    | 10
Name201   |    201    | 20
Name301   |    301    | 30
Name302   |    302    | 30

I certainly might do that with at least two queries, but how might I do that in a single statement?

我当然可以用至少两个查询来做到这一点,但是我怎么能在一个语句中做到这一点?

Another related problem is that I'd like to sort the result by author names with accent-insensitive order (where, e.g., ú = ü = u). I am aware about COLLATE Latin1_General_CI_AI, and it works fine in LIKE clause, however when I write ORDER BY names COLLATE Latin1_General_CI_AI, the statement returns error.

另一个相关的问题是我想按照不区分重音的顺序对作者姓名进行排序(例如,ú=ü= u)。我知道COLLATE Latin1_General_CI_AI,它在LIKE子句中工作正常,但是当我写ORDER BY名称COLLATE Latin1_General_CI_AI时,该语句返回错误。

1 个解决方案

#1

There is no such thing as the "first" author on a SQL table. SQL tables represent unordered sets. The closest you can get in SQLite is to use rowid.

在SQL表上没有“第一”作者这样的东西。 SQL表表示无序集。在SQLite中最接近的是使用rowid。

select docId,
       (case when count(*) = 1 then min(name)
             when count(*) = 2
             then max(case when rowid = minrowid then name end) || ' & ' ||
                  max(case when rowid <> minrowid then name end)
             else max(case when rowid = minrowid then name end) || ', et al'
         end) as authors
from AuthorsTable a left join
     (select a2.docid, min(rowid) as minrowid
      from AuthorsTable a2
      group by a2.docid
     ) aa
     on a.docid = a2.docid
group by docid;

EDIT:

Based on the comment that the ordering is by authorid:

基于命令由authorid的评论:

select docId,
       (case when count(*) = 1 then min(name)
             when count(*) = 2
             then max(case when AuthorId = minAuthorId then name end) || ' & ' ||
                  max(case when AuthorId <> minAuthorId then name end)
             else max(case when AuthorId = minAuthorId then name end) || ', et al'
         end) as authors
from AuthorsTable a left join
     (select a2.docid, min(AuthorId) as minAuthorId
      from AuthorsTable a2
      group by a2.docid
     ) aa
     on a.AuthorId = a2.minAuthorId
group by docid;

#1