具有多个值的 MYSQL CASE THEN 语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10155876/
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
MYSQL CASE THEN statement with multiple values
提问by Semur Nabiev
I am trying go select multiple values with CASEstatement. I noticed we cannot do
我正在尝试使用CASE语句选择多个值。我注意到我们做不到
CASE
WHEN wall.type="bk"
THEN books.id1,books.id2, // and so on
END as column_1,
Is there a way to do THENwith multiple columns or do we need to simply write a bunch of CASE THENstatements? that seems messy
有没有办法THEN处理多列，或者我们是否需要简单地编写一堆CASE THEN语句？看起来很乱
回答by Mosty Mostacho
No, it is just a single value. Additionally, it is contradictory to use "multiple columns" and name those multiple columns as column_1, right? :)
不，它只是一个值。此外，使用“多列”并将这些多列命名为 是矛盾的column_1，对吗？:)
You can use another column to store the other id with (a similar case) and use nulls to represent the elsevalues, just like you're doing now.
您可以使用另一列来存储另一个 id（类似的情况）并使用空值来表示else值，就像您现在所做的一样。
Example:
例子：
CASE
WHEN wall.type="bk"
THEN books.id1
END as column_1,
CASE
WHEN wall.type="bk"
THEN books.id2
END as column_2
Check the official documentationfor more information.
查看官方文档以获取更多信息。
回答by aleroot
回答by Clary
And everything can be done, but it always depends on what you want to do. Below I'll show you a working example right after you have to take the data as an array and do what you want.
一切都可以做到，但这始终取决于您想做什么。在您必须将数据作为数组并做您想做的事情之后，我将在下面向您展示一个工作示例。
CREATE TABLE wall (`ident` int,`type` varchar(2), `order` int);
INSERT INTO wall (`ident`, `type`, `order`) VALUES
(40,'bk', 1),
(41,'bk', 5),
(42,'rt', 2),
(43,'bk', 3),
(44,'rt', 1);
CREATE TABLE books (`ident` int,`id1` int, `time` varchar(8), `id2` int);
INSERT INTO books (`ident`, `id1`, `time`, `id2`) VALUES
(40, 10, '18:07:00', 20),
(43, 11, '05:00:00', 21),
(44, 12, '21:01:00', 22),
(41, 13, '10:00:00', 23),
(42, 14, '23:10:00', 24);
#--------------------------
SELECT
CASE
WHEN wall.type='bk'
THEN CONCAT(books.id1,'-',books.id2)
END AS column_1
FROM wall JOIN books ON books.ident = wall.ident GROUP BY wall.ident ORDER BY wall.ident ASC;
Print:
打印：
column_1
1 10-20
2 13-23
3 NULL
4 11-21
5 NULL
Solution in action via this link: http://rextester.com/LHPI38373
通过此链接执行的解决方案：http: //rextester.com/LHPI38373
