具有多个值的MYSQL CASE THEN语句

I am trying go select multiple values with CASE statement. 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 THEN with multiple columns or do we need to simply write a bunch of CASE THEN statements? that seems messy

有没有办法用多列做或那么我们需要简单地写一堆CASE THEN语句？这看起来很乱

3 个解决方案

#1

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 else values, just like you're doing now.

您可以使用另一列来存储其他id（类似情况）并使用null来表示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 documentation for more information.

有关更多信息，请查看官方文档。

#2

No. CASE statement can only return a single value, so the only way to achieve what you want is duplicate the case ...

CASE语句只能返回一个值，所以实现你想要的唯一方法是重复这个案例......

The database server should be optimized and perform only one time the check on the same condition ...

数据库服务器应该优化，只执行一次相同条件的检查...

#3

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

#1