基于默认列与传入语言ID在多对多关联表返回1-2条记录

原查询返回4条记录的原因 #

原查询中，Book表对应1条图书记录，BookLocalization有2条该图书的本地化记录，BookLanguage也有2条该图书的可用语言记录。由于两次INNER JOIN仅通过BookId关联，没有关联LanguageId字段，导致两个子表的记录产生笛卡尔积：2（本地化记录）×2（可用语言记录）=4条组合结果，也就是每条本地化记录都会和每条可用语言记录配对，最终输出4条重复关联的记录。

需求实现方案 #

我们需要根据传入的语言ID，优先返回匹配的本地化记录；若该语言不在图书的可用语言列表中，则返回默认语言的本地化记录。以下是两种可行方案：

方案1：仅返回1条目标记录（优先匹配，无则默认） #

使用TOP 1结合优先级排序，确保只返回优先级最高的记录：

DECLARE @TargetLanguageId UNIQUEIDENTIFIER = '2F12CAA6-16D7-4D83-B17E-560241DAE1D2' -- 传入目标语言ID
DECLARE @BookId UNIQUEIDENTIFIER = 'D31A6823-5415-407F-9B49-49136242F03F' -- 指定图书ID

SELECT TOP 1
    B.[Id] AS BookId,
    B.[PublicDate],
    BL.[Id] AS BookLocalizationId,
    BL.[Name] AS LocalizedName,
    BL.[LanguageId],
    L.[Name] AS LanguageName,
    L.[Culture]
FROM [Book] B
INNER JOIN [BookLocalization] BL 
    ON BL.[BookId] = B.[Id]
INNER JOIN [Language] L 
    ON L.[Id] = BL.[LanguageId]
INNER JOIN [BookLanguage] BLang 
    ON BLang.[BookId] = B.[Id] 
    AND BLang.[LanguageId] = BL.[LanguageId]
WHERE B.[Id] = @BookId
ORDER BY 
    -- 设置优先级：匹配目标语言 > 默认语言
    CASE 
        WHEN BL.[LanguageId] = @TargetLanguageId THEN 0
        WHEN BLang.[Default] = 1 THEN 1
        ELSE 2 
    END ASC

逻辑说明：

• 关联所有表时，通过BLang.[LanguageId] = BL.[LanguageId]确保只查询图书可用语言对应的本地化记录
• 利用CASE语句给记录设置优先级：目标语言匹配的记录优先级最高（排序值0），默认语言次之（排序值1）
• TOP 1筛选出优先级最高的唯一记录

方案2：返回2条记录（优先匹配+默认，无重复） #

如果需要同时返回匹配记录（存在时）和默认记录（仅当匹配记录不存在时），可以用UNION组合两个查询：

DECLARE @TargetLanguageId UNIQUEIDENTIFIER = '895E0F72-413C-48CD-A1A1-6302AC8A4CB4' -- 传入目标语言ID
DECLARE @BookId UNIQUEIDENTIFIER = 'D31A6823-5415-407F-9B49-49136242F03F' -- 指定图书ID

-- 1. 查询匹配目标语言的记录（仅当该语言在图书可用列表中）
SELECT
    B.[Id] AS BookId,
    B.[PublicDate],
    BL.[Id] AS BookLocalizationId,
    BL.[Name] AS LocalizedName,
    BL.[LanguageId],
    L.[Name] AS LanguageName,
    L.[Culture],
    'Matched Language' AS SourceType
FROM [Book] B
INNER JOIN [BookLocalization] BL 
    ON BL.[BookId] = B.[Id] 
    AND BL.[LanguageId] = @TargetLanguageId
INNER JOIN [Language] L 
    ON L.[Id] = BL.[LanguageId]
INNER JOIN [BookLanguage] BLang 
    ON BLang.[BookId] = B.[Id] 
    AND BLang.[LanguageId] = BL.[LanguageId]
WHERE B.[Id] = @BookId

UNION

-- 2. 查询默认语言的记录（仅当目标语言不存在时）
SELECT
    B.[Id] AS BookId,
    B.[PublicDate],
    BL.[Id] AS BookLocalizationId,
    BL.[Name] AS LocalizedName,
    BL.[LanguageId],
    L.[Name] AS LanguageName,
    L.[Culture],
    'Default Language' AS SourceType
FROM [Book] B
INNER JOIN [BookLanguage] BLang 
    ON BLang.[BookId] = B.[Id] 
    AND BLang.[Default] = 1
INNER JOIN [BookLocalization] BL 
    ON BL.[BookId] = B.[Id] 
    AND BL.[LanguageId] = BLang.[LanguageId]
INNER JOIN [Language] L 
    ON L.[Id] = BL.[LanguageId]
WHERE B.[Id] = @BookId
AND NOT EXISTS (
    SELECT 1 FROM [BookLanguage] 
    WHERE BookId = @BookId AND LanguageId = @TargetLanguageId
)

逻辑说明：

• 第一个查询仅返回与目标语言匹配的记录（需同时存在于BookLanguage和BookLocalization）
• 第二个查询通过NOT EXISTS判断，仅当目标语言不在图书可用列表中时，返回默认语言的记录
• UNION自动去重，避免当目标语言就是默认语言时出现重复记录

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