寻求无需UNION的多语言文本块默认值SQL视图实现方案

问题背景 #

现有三张表：Language、TextBlock、TextBlocki18n，其中TextBlock默认存储en-US语言的内容。需要创建SQL视图，为每种语言展示对应翻译内容，无翻译时显示en-US的默认值。当前已有12种语言，还将新增至少12种，原有的UNION方案扩展性极差，急需更优实现方式。

表结构示例 #

Language表 #

LangCode | LangName
en-US    | English (US)
fr-FR    | French (France)
es-ES    | Spanish (Spain)

TextBlock表 #

ID | Content
1  | One
2  | Two
3  | Three

TextBlocki18n表 #

TbFK | LangFK | Content
1    | fr-CA  | Une
1    | es-ES  | Uno
2    | es-ES  | Dos
3    | fr-CA  | Trois

期望视图输出 #

TbID | LangCode | Content
1    | en-US    | One
2    | en-US    | Two
3    | en-US    | Three
1    | fr-CA    | Une
2    | fr-CA    | Two
3    | fr-CA    | Trois
1    | es-ES    | Uno
2    | es-ES    | Dos
3    | es-ES    | Three

优雅实现方案 #

核心思路是通过交叉连接生成所有文本块与语言的组合，再左关联翻译表获取对应内容，用COALESCE自动 fallback 到默认值。新增语言时只需维护Language表，视图无需修改。

视图创建SQL #

CREATE VIEW TextBlockLocalized AS
SELECT
    tb.ID AS TbID,
    l.LangCode,
    COALESCE(tbi.Content, tb.Content) AS Content
FROM TextBlock tb
CROSS JOIN Language l
LEFT JOIN TextBlocki18n tbi
    ON tb.ID = tbi.TbFK
    AND l.LangCode = tbi.LangFK;

方案解析 #

• CROSS JOIN：生成TextBlock每个条目与Language每种语言的全量组合，确保所有语言都被覆盖，无需手动逐个添加语言分支。
• LEFT JOIN 翻译表：按文本块ID和语言代码匹配翻译内容，无对应翻译时返回NULL。
• COALESCE函数：优先使用翻译内容，若翻译不存在则回退到TextBlock中的en-US默认值，满足需求。

内容的提问来源于stack exchange，提问作者JayTee