SQLite;看法左边连接两张桌子;如果在给定语言中存在翻译，则使用联接表的翻译动态替换值

Question

什么是view/SQL查询，以实现标题中所述的内容。

为相关表创建代码：

CREATE TABLE "books" (
"bookId"    integer NOT NULL PRIMARY KEY AUTOINCREMENT,
"bookName"  nvarchar NOT NULL COLLATE NOCASE
);
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

BookLang表中的数据：

bookLangId      BookId     LanguageID     BookName_Localization
1               1          12             Genèse
2               2          12             Exode
3               1          10             Schöpfung
4               4          10             Zahlen

目标：查询/视图应具有与图书表相同的字段(BookId、BookName(，并始终为图书表中的每条记录输出一行，但如果给定语言有本地化(翻译(版本且BookId相同，则动态替换BooksLang表中BookName的值。如果没有替代项，BookName的输出值应该是books表中记录的值。

示例：如果对此视图的查询如下所示：select * from <view> where LanguageID = 12

那么输出应该是：

bookId     BookName
1          Genèse       (substituted from booksLang table)
2          Exode        (substituted from booksLang table)
3          Leviticus    (original from books table because no substitute for given language available)
4          Numbers      (original from books table because no substitute for given language available)
5          Deuteronomy  (original from books table because no substitute for given language available)

由于我不是SQL专家，如何做到这一点？

提示：当languageID作为左联接的一部分提供时，下面的查询将检索所需的结果，但如何从查询该视图的查询中动态地执行此操作？

select b.bookId, 
(CASE
WHEN bl.bookName is null
THEN b.bookName
ELSE bl.bookname
END) as bookName
from books b left join
bookslang bl
on bl.BookId = b.BookId and bl.languageId = 12

Answer 1

创建此视图：

create view books_langs as
select b.bookId, coalesce(bl.bookName, b.bookName) as bookName, d.languageId
from (select id as languageId from languages) d cross join 
books b left join bookslang bl
on bl.BookId = b.BookId and bl.languageId = d.languageid;

然后像这样查询：

select bookid, bookname 
from books_langs
where languageid = ?

请参阅演示