场景如下(我使用的是SQLite(:
主账簿:
CREATE TABLE "books" (
"bookId" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
"bookName" nvarchar NOT NULL COLLATE NOCASE
);
外键表书籍Lang:
CREATE TABLE "booksLang" (
"bookLangId" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
"BookId" INTEGER NOT NULL,
"LanguageID" INTEGER NOT NULL,
"BookName" TEXT
);
账簿中的数据:
bookId BookName
1 Genesis
2 Exodus
3 Leviticus
4 Numbers
5 Deuteronomy
书籍中的数据Lang:
bookLangId BookId LanguageID BookName_Localization
1 1 12 Genèse
2 2 12 Exode
3 1 10 Schöpfung
4 4 10 Zahlen
问题:当给定的语言ID为12,并且必须得出以下结果时,查询的外观如何(books和booksLang表"不知何故"连接在一起,但请告诉我如何连接(:
bookId BookName BookName_Localization
1 Genesis Genèse
2 Exodus Exode
3 Leviticus ----- (none because there is no localization)
4 Numbers ----- (none because there is no localization)
5 Deuteronomy ----- (none because there is no localization)
如果languageID为10,则结果必须如下所示:
bookId BookName BookName_Localization
1 Genesis Schöpfung
2 Exodus ----- (none because there is no localization)
3 Leviticus ----- (none because there is no localization)
4 Numbers Zahlen
5 Deuteronomy ----- (none because there is no localization)
由于我不是SQL专家,我不知道如何总是从books表中检索所有记录,并从联接中检索BookName_Localization,但在其中检索"null"或类似的内容。
此外,可能有许多不同语言的本地化,但结果集必须始终只包含每个主记录的一个记录(不少于或多于(,并使用给定LanguageID的联接booksLang表中的BookName_Localization字段进行丰富。此附加字段的值要么包含给定语言的本地化文本(如果存在(,要么必须为空(如果不存在(。
非常感谢您的帮助。
*由于格式不正确而编辑
我认为您描述的是LEFT JOIN
:
select b.*, bl.bookname
from books b left join
bookslang bl
on bl.BookId = b.BookId and bl.languageId = 12