MySQL按语言ID对比记录：查找缺失指定语言翻译的行

首先，我们需要找出那些没有同时包含language_id=4和10翻译的分类记录，再整理成你需要的前端展示格式。我分两步来给你说明：

1. 找出缺失翻译的明细记录（SQL查询） #

先通过生成所有需要的「分类-语言」组合，再和原表左连接，就能明确看到哪些分类缺失了对应语言的翻译。用CTE（公共表表达式）能让逻辑更清晰：

WITH required_langs AS (
    -- 定义需要检查的两个目标语言ID
    SELECT 4 AS language_id UNION ALL SELECT 10 AS language_id
),
all_category_lang_pairs AS (
    -- 生成所有分类与目标语言的笛卡尔积，确保每个分类都对应两个语言的检查项
    SELECT DISTINCT t.categories_id, r.language_id
    FROM your_table_name t
    CROSS JOIN required_langs r
)
SELECT 
    a.categories_id,
    a.language_id,
    -- 有翻译就显示名称，缺失则显示占位文本
    COALESCE(t.categories_name, '[add translation]') AS categories_name
FROM all_category_lang_pairs a
LEFT JOIN your_table_name t 
    ON a.categories_id = t.categories_id 
    AND a.language_id = t.language_id
-- 过滤掉已经拥有两种语言完整翻译的分类
WHERE NOT EXISTS (
    SELECT 1 
    FROM your_table_name t2 
    WHERE t2.categories_id = a.categories_id 
    AND t2.language_id IN (4,10)
    GROUP BY t2.categories_id
    HAVING COUNT(DISTINCT t2.language_id) = 2
)
ORDER BY a.categories_id, a.language_id;

这个查询会返回每个缺失翻译的分类对应的两行记录（对应两个语言ID），缺失的翻译位置会自动填充[add translation]。

2. 直接生成前端需要的展示格式 #

如果想一步到位生成TRANSLATION 1 - TRANSLATION 2的格式，可以用条件聚合把同一分类的两个语言值合并到一行：

WITH required_langs AS (
    SELECT 4 AS language_id UNION ALL SELECT 10 AS language_id
),
all_category_lang_pairs AS (
    SELECT DISTINCT t.categories_id, r.language_id
    FROM your_table_name t
    CROSS JOIN required_langs r
),
category_translations AS (
    SELECT 
        a.categories_id,
        -- TRANSLATION 1对应language_id=10的翻译内容
        MAX(CASE WHEN a.language_id = 10 THEN COALESCE(t.categories_name, '[add translation]') END) AS translation_1,
        -- TRANSLATION 2对应language_id=4的翻译内容
        MAX(CASE WHEN a.language_id = 4 THEN COALESCE(t.categories_name, '[add translation]') END) AS translation_2
    FROM all_category_lang_pairs a
    LEFT JOIN your_table_name t 
        ON a.categories_id = t.categories_id 
        AND a.language_id = t.language_id
    WHERE NOT EXISTS (
        SELECT 1 
        FROM your_table_name t2 
        WHERE t2.categories_id = a.categories_id 
        AND t2.language_id IN (4,10)
        GROUP BY t2.categories_id
        HAVING COUNT(DISTINCT t2.language_id) = 2
    )
    GROUP BY a.categories_id
)
SELECT CONCAT(translation_1, ' - ', translation_2) AS display_text
FROM category_translations;

执行这个查询后，会直接得到你需要的结果：

Bike - [add translation]
[add translation] - Bil

你可以把这个查询结果直接返回给前端，或者在后端处理成对应的展示文本即可。

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