MySQL select * from table where column = something 或者，当不可用时， column = 别的东西
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7473289/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
select * from table where column = something or, when unavailable, column = something else
提问by Bohemian
I am looking for something more efficient and/or easier to read than the query that follows. The best way to explain the question is to provide a sample.
我正在寻找比接下来的查询更有效和/或更容易阅读的东西。解释这个问题的最好方法是提供一个样本。
Assume the following table structure in MySQL that represents, say, a localization context for various strings in the application I am creating.
假设 MySQL 中的以下表结构表示我正在创建的应用程序中各种字符串的本地化上下文。
create table LOCALIZATION_TABLE (
SET_NAME varchar(36) not null,
LOCALE varchar(8) not null default '_',
ENTRY_KEY varhcar(36) not null,
ENTRY_VALUE text null
);
alter table LOCALIZATION_TABLE
add constraint UQ_ENTRY
unique (SET_NAME, LOCALE, ENTRY_KEY);
Assume the following values are entered in the table:
假设在表中输入了以下值：
insert into LOCALIZATION_TABLE (SET_NAME, LOCALE, ENTRY_KEY, ENTRY_VALUE)
values
('STD_TEXT', '_', 'HELLO', 'Hello!'),
('STD_TEXT', '_', 'GOODBYE', 'Goodbye.'),
('STD_TEXT', 'ge', 'GOODBYE', 'Lebewohl')
;
I want to select all the available entries for German ("ge"), and if not available use the English text ("_") by default. The query I am currently using is as follows:
我想选择所有可用的德语条目（“ge”），如果不可用，默认情况下使用英文文本（“_”）。我目前使用的查询如下：
select * from LOCALIZATION_TABLE where SET_NAME = 'STD_TEXT' and LOCALE = 'ge'
union
select * from LOCALIZATION_TABLE where SET_NAME = 'STD_TEXT' and LOCALE = '_'
and ENTRY_KEY not in (
select ENTRY_KEY from LOCALIZATION_TABLE where BUNDLE = 'STD_TEXT' and LOCALE = 'ge'
)
I reallydo not like the look of this query and I am certain there must be something more concise that can be utilized instead. Any help or clues to the right direction would be appreciated. While this works it just does not seem proper.
我真的不喜欢这个查询的外观，我确信必须有一些更简洁的东西可以代替。任何帮助或正确方向的线索将不胜感激。虽然这行得通，但似乎并不合适。
回答by Bohemian
You can provide a custom ordering, then take the first row, like this:
您可以提供自定义排序，然后取第一行，如下所示：
select * from (
select *
from LOCALIZATION_TABLE
where SET_NAME = 'STD_TEXT'
order by field(LOCALE, 'ge', '_') -- this provides the custom ordering
) x
group by ENTRY_KEY; -- this captures the first row for each ENTRY_KEY
Explanation:
解释：
The inner select's order by field(LOCALE, 'ge', '_')gets you the rows in the order you define - in this case German first if it exists, then English (you could add more languages to the list).
内部选择order by field(LOCALE, 'ge', '_')按您定义的顺序为您提供行 - 在这种情况下，首先是德语（如果存在），然后是英语（您可以向列表中添加更多语言）。
The "trick" here is using mysql's "non-standard" GROUP BYbehaviour when notlisting the non-group columns (most servers treat this as a syntax error) - it simply returns the first row found for every group. The outer select uses group bywithoutan aggregate to get the first rowfor each named group by.
这里的“技巧”是GROUP BY在不列出非组列时使用 mysql 的“非标准”行为（大多数服务器将其视为语法错误） - 它只是返回为每个组找到的第一行。外部选择使用group by没有聚合来获取每个命名组的第一行。
Output of this query using your data:
此查询使用您的数据的输出：
+----------+--------+-----------+-------------+
| SET_NAME | LOCALE | ENTRY_KEY | ENTRY_VALUE |
+----------+--------+-----------+-------------+
| STD_TEXT | ge | GOODBYE | Lebewohl |
| STD_TEXT | _ | HELLO | Hello! |
+----------+--------+-----------+-------------+
回答by ypercube??
I think your query is fine. But here's another approach:
我认为您的查询很好。但这是另一种方法：
SELECT
en.SET_NAME
, COALESCE(ge.LOCALE, en.LOCALE)
, en.ENTRY_KEY
, COALESCE(ge.ENTRY_VALUE, en.ENTRY_VALUE)
FROM
LOCALIZATION_TABLE AS en
LEFT JOIN
LOCALIZATION_TABLE AS ge
ON ge.ENTRY_KEY = en.ENTRY_KEY
AND ge.LOCALE = 'ge'
AND ge.SET_NAME = 'STD_TEXT'
WHERE
en.LOCALE = '_'
AND en.SET_NAME = 'STD_TEXT'
