sqlite - 从不同的表中获取结果 - 加入

Question

我的数据库中有以下表格

CREATE TABLE [author_details] (
[_id] INTEGER  PRIMARY KEY AUTOINCREMENT NOT NULL,
[name] TEXT  NOT NULL,
[surname] TEXT  NOT NULL,
[middle_name] TEXT  NULL
);

CREATE TABLE [authors] (
[_id] INTEGER  NOT NULL PRIMARY KEY AUTOINCREMENT,
[book_id] INTEGER  NOT NULL,
[author_id] INTEGER  NOT NULL
);

CREATE TABLE [books] (
[_id] INTEGER  PRIMARY KEY AUTOINCREMENT NOT NULL,
[title] TEXT  NOT NULL,
[publisher_id] INTEGER  NOT NULL,
[isbn] VARCHAR(10)  UNIQUE NULL,
[ean] VARCHAR(13)  UNIQUE NULL,
[pages] INTEGER DEFAULT '0' NULL,
[year] INTEGER  NOT NULL,
[edition] TEXT  NULL
);

CREATE TABLE [publishers] (
[_id] INTEGER  NOT NULL PRIMARY KEY AUTOINCREMENT,
[name] TEXT  NOT NULL
);

我想要一个包含详细信息的所有书籍的列表，我使用了以下查询:

SELECT b.title,b.isbn,b.ean,b.year,b.pages,b.edition,
    CASE
        WHEN ad.middle_name IS NULL
            THEN ad.name||" "||ad.surname
            ELSE ad.name||" "||ad.middle_name||" "||ad.surname
    END AS author, p.name
FROM books AS b, authors AS a, author_details AS ad, publishers AS p
INNER JOIN authors, author_details, publishers ON b._id=a.book_id AND ad._id=a.author_id AND b.publisher_id=p._id
GROUP BY b._id

对于有多个作者的书籍，它返回所有书籍，但只返回一个作者。如何编写查询以获取每本书的所有作者？

Answer 1

要从组中的所有记录中获取值，您必须使用 group_concat功能:

SELECT b.title,b.isbn,b.ean,b.year,b.pages,b.edition,
       group_concat(CASE
                    ...
                    END) AS author, p.name
FROM ...

此外，您需要使用正确的 join syntax .
在您的查询中，您将每个表加入两次，这会导致大量重复记录。

连接有两种等效的语法。
要么使用简单的表列表，要么使用:

...
FROM books          AS b,
     authors        AS a,
     author_details AS ad,
     publishers     AS p
WHERE b._id          = a.book_id
  AND a.author_id    = ad._id
  AND b.publisher_id = p._id
...

或者对每个连接使用 JOIN 运算符，每个连接都有一个连接条件:

...
FROM books          AS b
JOIN authors        AS a  ON b._id          = a.book_id
JOIN author_details AS ad ON a.author_id    = ad._id
JOIN publishers     AS p  ON b.publisher_id = p._id
...