mysql - 同一查询中的多个 SELECT 会覆盖彼此的结果

Question

抱歉标题令人困惑，最好直接看代码:

示例表:

|     key    |    value  |    MainLanguage     |     SubLanguage     |
|    mLangID |      5    |       default       |         en          |
|    mLangID |      3    |         esES        |         en          |
|    mTitle  |  Title    |       default       |         en          |
|    mTitle  |  Título   |         esES        |         es          |

我想避免使用这些查询:

SELECT * FROM translation.strings WHERE MainLanguage = 'esES' AND lang='es'
SELECT * FROM translation.strings WHERE MainLanguage = 'esES' AND lang='en'
SELECT * FROM translation.strings WHERE MainLanguage = 'default' AND lang='en'
...

并将它们组合成一个，但要确保第一个查询的结果不会被第二个查询的结果覆盖，依此类推。或者，如果更简单，则相反(第二个查询覆盖第一个查询的结果，第三个查询覆盖第二个查询，等等)

Answer 1

如果我理解正确，您希望将 union all 与 where 子句来确定结果的优先级。我猜你想要每个键一行。如果是这样，这可能会起作用

SELECT *
FROM translation.strings s
WHERE MainLanguage = 'esES' AND lang = 'es'
UNION ALL
SELECT *
FROM translation.strings s
WHERE MainLanguage = 'esES' AND lang = 'en' AND
      NOT EXISTS (select 1
                  from translation.strings s2
                  where s2.key = s.key and s2.MainLanguage = 'esES' AND s2.lang = 'es'
                 )
UNION ALL
SELECT *
FROM translation.strings s
WHERE MainLanguage = 'default' AND lang = 'en' AND
      NOT EXISTS (select 1
                  from translation.strings s2
                  where s2.key = s.key and
                        (s2.MainLanguage = 'esES' AND s2.lang = 'es' or
                         s2.MainLanguage = 'esES' AND s2.lang='en'
                        )
                 );

请注意:在其他数据库中有更简单的方法可以解决此问题。