MySQL LEFT JOIN与OR条件的Fallback需求实现问询

现有表结构与数据 #

webpages表数据如下：

当前SQL问题 #

现有关联查询SQL：

SELECT `companies`.*, `webpages`.`lang`, `webpages`.`URL`
FROM `companies` LEFT JOIN `webpages`
ON `companies`.`companyID` = `webpages`.`companyID`
AND (`webpages`.`lang` = :lang OR `webpages`.`lang` IS NULL)

该语句会返回同一公司的多条匹配记录，无法满足每个公司仅返回一条最优结果的需求：

• 传入lang='hu'时，需仅返回公司1的hu语言URL
• 传入lang='de'时，需返回公司1/3的一条NULL回退URL
• 传入lang='en'时，返回公司1/4的en语言URL

单SQL解决方案 #

利用窗口函数ROW_NUMBER()给匹配记录标记优先级，优先取目标语言的记录，无匹配则取回退URL，最终每个公司仅保留一条结果：

SELECT c.*, wp.lang, wp.URL
FROM companies c
LEFT JOIN (
    SELECT 
        companyID, 
        lang, 
        URL,
        ROW_NUMBER() OVER (
            PARTITION BY companyID 
            ORDER BY CASE WHEN lang = :lang THEN 1 ELSE 2 END, URL
        ) AS rn
    FROM webpages
    WHERE lang = :lang OR lang IS NULL
) wp ON c.companyID = wp.companyID AND wp.rn = 1;

逻辑说明 #

1. 子查询中：
   • 筛选出目标语言或回退（lang=NULL）的记录
   • 按companyID分组，给每组记录排序：目标语言的行标记为优先级1，回退URL标记为2；同优先级下按URL排序保证结果稳定
   • 用ROW_NUMBER()给每组内的记录编号，最优记录的编号为1
2. 外层查询关联子查询，仅取每组编号为1的记录，实现每个公司仅一条最优结果

测试验证：

• 传入lang='hu'：公司1返回hu.example.com/x，公司3返回www.example.com/y，公司4无匹配则URL为NULL
• 传入lang='de'：公司1返回一条NULL回退URL（如www.example.com/x），公司3返回www.example.com/y，公司4无匹配则URL为NULL
• 传入lang='en'：公司1返回en.example.com/x，公司4返回en.example.com/z，公司3返回www.example.com/y

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