你可以在句子中加一个子句 WHERE 如果意大利语翻译不存在，它将使用 NOT EXISTS 条款：

WHERE 
    `tag_l11n_2`.`tag_l11n_language` = 'it' 
OR
    `tag_l11n_2`.`tag_l11n_language` = 'en'
    AND NOT EXISTS (SELECT *
                    FROM tag_l11n t3 
                    WHERE t3.tag_l11n_tag = tag_3.tag_id 
                      AND t3.tag_l11n_language = 'it')

另一个（可能更高的性能，因为它不会有一个 OR 条件）解决方案是 LEFT JOIN 至 tag_l11n_2 两次，一次用于所需语言，一次用于备份，并使用 COALESCE 要确定所需语言结果的优先级，请执行以下操作：

SELECT 
    `tag_3`.`tag_id` as tag_id_3,
    COALESCE(`tag_l11n_2`.`tag_l11n_title`, `tag_l11n_3`.`tag_l11n_title`) as tag_l11n_title_2
FROM 
    `tag` as tag_3 
LEFT JOIN 
    `tag_l11n` as tag_l11n_2 ON `tag_3`.`tag_id` = `tag_l11n_2`.`tag_l11n_tag` 
                            AND `tag_l11n_2`.`tag_l11n_language` = 'it' 
LEFT JOIN 
    `tag_l11n` as tag_l11n_3 ON `tag_3`.`tag_id` = `tag_l11n_3`.`tag_l11n_tag` 
                            AND `tag_l11n_3`.`tag_l11n_language` = 'en' 
ORDER BY 
    `tag_3`.`tag_id` ASC 
LIMIT 25;

注意：通过添加 LEFT JOIN 和相应的列 COALESCE .
dbfiddle上的（两个查询的）演示

如果您运行的是mysql 8.0，我建议您 row_number() 要处理优先级排序：

SELECT tag_id_3, tag_l11n_title_2
FROM (
    SELECT 
        t.`tag_id` as tag_id_3,
        tl.`tag_l11n_title` as tag_l11n_title_2,
        ROW_NUMBER() OVER(PARTITION BY t.`tag_id` ORDER BY
            (tl.`tag_l11n_language` = 'it') desc,
            (tl.`tag_l11n_language` = 'en') desc,
            tl.`tag_l11n_language`
        ) rn
    FROM `tag` as t
    LEFT JOIN `tag_l11n` as tl ON t.`tag_id` = tl.`tag_l11n_tag` 
) t
WHERE rn = 1
ORDER BY t.`tag_id`
LIMIT 25;

有了这个解决方案，您可以根据需要管理任意级别的优先级，方法是向 ORDER BY 条款 ROW_NUMBER() . 目前这把意大利语翻译放在第一位，然后是英语翻译，然后是任何其他可用的翻译（按字母顺序）。

where条件将left更改为内部联接，导致在没有行的情况下不返回任何行 en .
经典的解决方案使用一种left join perm语言，然后合并得到第一个非空值：

SELECT 
    `tag_3`.`tag_id` as tag_id_3,
    coalesce(`tag_l11n_it`.`tag_l11n_title`  -- same order as join order
            ,`tag_l11n_en`.`tag_l11n_title`
            ,`tag_l11n_2`.`tag_l11n_title`) as tag_l11n_title_2
FROM 
    `tag` as tag_3 
LEFT JOIN 
    `tag_l11n` as tag_l11n_it
ON `tag_3`.`tag_id` = `tag_l11n_it`.`tag_l11n_tag` 
AND -- WHERE changes the LEFT join to INNER 
    `tag_l11n_it`.`tag_l11n_language` = 'it'  -- main language

LEFT JOIN 
    `tag_l11n` as tag_l11n_en 
ON `tag_3`.`tag_id` = `tag_l11n_en`.`tag_l11n_tag` 
AND
    `tag_l11n_en`.`tag_l11n_language` = 'en'  -- 2nd language

LEFT JOIN 
    `tag_l11n` as tag_l11n_2
ON `tag_3`.`tag_id` = `tag_l11n_2`.`tag_l11n_tag` 
AND
    `tag_l11n_2`.`tag_l11n_language` = 'de'   -- default language
ORDER BY 
    `tag_3`.`tag_id` ASC 
LIMIT 25;

虽然gmb的解决方案在没有默认语言的情况下工作，但是这个解决方案需要默认语言（没有缺少翻译的语言），在您的情况下可能是这样 de :-)
也许gmb的查询可以通过在连接之前应用行号来改进：

SELECT tag_id_3, tag_l11n_title_2
FROM `tag` as t
LEFT JOIN
 (
    SELECT 
        `tag_l11n_tag` as tag_id_3,
        `tag_l11n_title` as tag_l11n_title_2,
        ROW_NUMBER()
        OVER(PARTITION BY t.`tag_id`
             ORDER BY
               CASE WHEN `tag_l11n_language` = 'it' THEN 1
                  WHEN `tag_l11n_language` = 'en' THEN 2
                  ELSE 3
               END
            ,tl.`tag_l11n_language`) AS rn
    FROM `tag_l11n`
  ) AS t1
ON t.`tag_id` = tl.`tag_l11n_tag` 
WHERE t1.rn = 1
ORDER BY t.`tag_id`
LIMIT 25;

这个优先级逻辑也可以用于聚合：

SELECT tag_id_3, tag_l11n_title_2
FROM `tag` as t
LEFT JOIN -- INNER JOIN should be possible
 (
    SELECT 
        `tag_l11n_tag` as tag_id_3,
        COALESCE(MAX(CASE WHEN `tag_l11n_language` = 'it' THEN `tag_l11n_title` END)
                ,MAX(CASE WHEN `tag_l11n_language` = 'en' THEN `tag_l11n_title` END)
                ,MAX(CASE WHEN `tag_l11n_language` = 'de' THEN `tag_l11n_title` END)
                -- if there's no default you can get a random value using ,MAX(`tag_l11n_title`)
                ) AS tag_l11n_title_2
    FROM `tag_l11n`
    GROUP BY `tag_l11n_tag`
  ) AS t1
ON t.`tag_id` = tl.`tag_l11n_tag` 
ORDER BY t.`tag_id`
LIMIT 25; -- might be possible to move into Derived Table

多亏了@nick answer，我想出了使用函数的新点子 FIELD() 它将覆盖所有可能的语言，而不为每种语言连接单独的表，但有一个子选择。与其他答案相比，我不确定性能如何，但如果用数值来索引语言会比字符串更快( en , it 等等）。
当然，在subselect中应该排除带有null的翻译。

SELECT 
    `tag`.`tag_id` as tag_id,
    COALESCE(`tag_l11n`.`tag_l11n_title`,"No translation") as tag_l11n_title
FROM 
    `tag` as tag
LEFT JOIN (
      SELECT
          `tag_l11n_tag` as tag_id,
          `tag_l11n_title` as tag_l11n_title,
      FROM 
          `tag_l11n`
      WHERE 
          `tag_l11n_title` IS NOT NULL,
      ORDER BY
          FIELD(`tag_l11n_language`,"it","en","es")
      LIMIT 1
      ) as tag_l11n ON `tag_l11n`.`tag_id` = `tag`.`tag_id`
ORDER BY 
    `tag`.`tag_id` ASC