Postgres：基于布尔值的SELECT列名称 [英] Postgres: SELECT column name based on Boolean value

问题描述

我有一个表格，其中包含旅行模式的布尔值，如下所示：

I have a table containing Boolean values for travel modes, which looks like:

SELECT * FROM survey;
+----+-------+-------+-------+-------+
| id | bike  |  car  |  bus  | metro |
+----+-------+-------+-------+-------+
| 49 | false | false | true  | false |
| 51 | false | true  | false | false |
| 52 | false | false | false | true  |
| 53 | false | true  | false | false |
+----+-------+-------+-------+-------+

然后我只选择值为 true 的模式，这样最终结果是：

Then I want select only the modes for which the value is true, so that the end result is:

+----+-------+
| id | mode  |
+----+-------+
| 49 | bus   |
| 51 | car   |
| 52 | metro |
| 53 | car   |
+----+-------+

我如何实现

推荐答案

您可以使用 case 表达式：

You can use a case expression:

select
    id,
    case
        when bike  = true then 'bike'
        when car   = true then 'car'
        when bus   = true then 'bus'
        when metro = true then 'metro'
    end mode
from survey

这假设对于每一行，只有一列为真。否则，将仅返回第一个匹配列的值。

This supposes that for each row, only one column is true. If not, only the value of the first matching column will be returned.

这篇关于Postgres：基于布尔值的SELECT列名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！