SQL 在 PostgreSQL 中使用 CASE 一次影响多个列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13713316/
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
Using CASE in PostgreSQL to affect multiple columns at once
提问by Elliot B.
I have a Postgres SELECTstatement with these expressions:
我有一个SELECT带有这些表达式的 Postgres语句：
,CASE WHEN (rtp.team_id = rtp.sub_team_id)
THEN 'testing'
ELSE TRIM(rtd2.team_name)
END AS testing_testing
,CASE WHEN (rtp.team_id = rtp.sub_team_id)
THEN 'test example'
ELSE TRIM(rtd2.normal_data)
END AS test_response
,CASE WHEN (rtp.team_id = rtp.sub_team_id)
THEN 'test example #2'
ELSE TRIM(rtd2.normal_data_2)
END AS another_example
In my particular query there are 5 fields whose output depends on whether rtp.team_id = rtp.sub_team_idevaluates true. I'm repeating CASEstatements with the same condition over and over.
在我的特定查询中，有 5 个字段的输出取决于rtp.team_id = rtp.sub_team_id评估结果是否为真。我CASE一遍又一遍地重复具有相同条件的语句。
Is there any way I can combine these CASEexpressions to toggle the output of multiple columns in one shot?
有什么方法可以组合这些CASE表达式来一次性切换多列的输出？
回答by Erwin Brandstetter
1. Standard-SQL: LEFT JOINa single row of values
1. Standard-SQL：LEFT JOIN单行值
You could LEFT JOINa row of values using the condition (thereby evaluating it once). Then you can add fallback values per column with COALESCE().
您可以LEFT JOIN使用条件的一行值（从而评估一次）。然后，您可以使用COALESCE().
This syntax variant is shorter and slightly faster with multiple values - especially interesting for an expensive / lengthy condition:
此语法变体更短且速度更快，具有多个值 - 对于昂贵/冗长的条件尤其有趣：
SELECT COALESCE(x.txt1, trim(r2.team_name)) AS testing_testing
, COALESCE(x.txt2, trim(r2.normal_data)) AS test_response
, COALESCE(x.txt3, trim(r2.normal_data_2)) AS another_example
FROM rtp
JOIN rtd2 r2 ON <unknown condition> -- missing context in question
LEFT JOIN (
SELECT 'testing'::text AS txt1
, 'test example'::text AS txt2
, 'test example #2'::text AS txt3
) x ON rtp.team_id = rtp.sub_team_id;
Since the derived table xconsists of a singlerow, joining without further conditions is fine.
由于派生表x由单行组成，因此无需其他条件就可以进行连接。
Explicit type castsare necessary in the subquery. I use textin the example (which is the default for string literals anyway). Use your actual data types. The syntax shortcut value::typeis Postgres-specific, use cast(value AS type)for standard SQL.
子查询中需要显式类型转换。我text在示例中使用（无论如何这是字符串文字的默认值）。使用您的实际数据类型。语法快捷方式value::type是 Postgres 特定的，cast(value AS type)用于标准 SQL。
If the condition is not TRUE, all values in xare NULL, and COALESCEkicks in.
如果条件不是TRUE，则中的所有值x均为 NULL，并COALESCE启动。
Or, since all candidate values come from table rtd2in your particular case, LEFT JOINto rtd2using the original CASEcondition and CROSS JOINto a row with default values:
或者，因为所有候选值来自表rtd2你的具体情况，LEFT JOIN来rtd2使用原来的CASE状态，并CROSS JOIN以默认值的行：
SELECT COALESCE(trim(r2.team_name), x.txt1) AS testing_testing
, COALESCE(trim(r2.normal_data), x.txt2) AS test_response
, COALESCE(trim(r2.normal_data_2), x.txt3) AS another_example
FROM rtp
LEFT JOIN rtd2 r2 ON <unknown condition> -- missing context in question
AND rtp.team_id = rtp.sub_team_id
CROSS JOIN (
SELECT 'testing'::text AS txt1
, 'test example'::text AS txt2
, 'test example #2'::text AS txt3
) x;
It depends on the join conditions and the rest of the query.
这取决于连接条件和查询的其余部分。
2. PostgreSQL-specific
2. PostgreSQL 特有的
2a. Expand an array
2a. 展开数组
If your various columns share the same data type, you can use an array in a subquery and expand it in the outer SELECT:
如果您的各个列共享相同的数据类型，您可以在子查询中使用数组并在外部扩展它SELECT：
SELECT x.combo[1], x.combo[2], x.combo[3]
FROM (
SELECT CASE WHEN rtp.team_id = rtp.sub_team_id
THEN '{test1,test2,test3}'::text[]
ELSE ARRAY[trim(r2.team_name)
, trim(r2.normal_data)
, trim(r2.normal_data_2)]
END AS combo
FROM rtp
JOIN rtd2 r2 ON <unknown condition>
) x;
It gets more complicated if the columns don't share the same data type. You can either cast them all to text(and optionally convert back in the outer SELECT), or you can ...
如果列不共享相同的数据类型，它会变得更加复杂。您可以将它们全部text转换为（并可选择在外部转换回SELECT），或者您可以......
2b. Decompose a row type
2b. 分解行类型
You can use a custom composite type (row type) to hold values of various types and simply *-expand it in the outer SELECT. Say we have three columns: text, integerand date. For repeateduse, create a custom composite type:
您可以使用自定义复合类型（行类型）来保存各种类型的值，并在外部简单地 *-expand 它SELECT。假设我们有三列：text,integer和date。为了重复使用，创建一个自定义的复合类型：
CREATE TYPE my_type (t1 text, t2 int, t3 date);
Orif the type of an existing table matches, you can just use the table name as composite type.
或者，如果现有表的类型匹配，您可以只使用表名作为复合类型。
Orif you only need the type temporarily, you can create a TEMPORARY TABLE, which registers a temporary type for the duration of your session:
或者，如果您只需要临时类型，您可以创建一个TEMPORARY TABLE，它会在您的会话期间注册一个临时类型：
CREATE TEMP TABLE my_type (t1 text, t2 int, t3 date);
You could even do this for a single transaction:
您甚至可以为单个事务执行此操作：
CREATE TEMP TABLE my_type (t1 text, t2 int, t3 date) ON COMMIT DROP;
Then you can use this query:
然后你可以使用这个查询：
SELECT (x.combo).* -- parenthesis required
FROM (
SELECT CASE WHEN rtp.team_id = rtp.sub_team_id
THEN ('test', 3, now()::date)::my_type -- example values
ELSE (r2.team_name
, r2.int_col
, r2.date_col)::my_type
END AS combo
FROM rtp
JOIN rtd2 r2 ON <unknown condition>
) x;
Or even just (same as above, simpler, shorter, maybe less easy to understand):
或者甚至只是（与上面相同，更简单，更短，可能不太容易理解）：
SELECT (CASE WHEN rtp.team_id = rtp.sub_team_id
THEN ('test', 3, now()::date)::my_type
ELSE (r2.team_name, r2.int_col, r2.date_col)::my_type
END).*
FROM rtp
JOIN rtd2 r2 ON <unknown condition>;
The CASEexpression is evaluated once for every column this way. If the evaluation is not trivial, the other variant with a subquery will be faster.
该CASE表达式一次，每列这样评价。如果评估不是微不足道的，带有子查询的另一个变体会更快。
回答by Daniel Lyons
Not sure that it would be an improvement, but you could union the SELECTone way with itself the other way:
不确定这是否会有所改进，但您可以将SELECT一种方式与另一种方式结合起来：
SELECT
...,
'testing' AS testing_testing,
'test example' AS test_response,
'test example #2' AS another_example, ...
FROM ...
WHERE rtp.team_id = rtp.sub_team_id AND ...
UNION
SELECT
...,
TRIM(rtd2.team_name) AS testing_testing,
TRIM(rtd2.normal_data) AS test_response,
TRIM(rtd2.normal_data_2) AS another_example, ...
WHERE rtp.team_id <> rtp.sub_team_id AND ...;
The column names can safely be omitted from the second query, assuming you bring them out in the same order as in the first.
列名可以安全地从第二个查询中省略，假设您以与第一个相同的顺序显示它们。
You may want to make each of those a separate query using common table expressions (CTEs). If you're worried about this changing the order, you can make it a subquery and apply an ORDER BYaround it.
您可能希望使用公共表表达式 (CTE) 将其中的每一个作为单独的查询。如果您担心这会更改顺序，则可以将其设为子查询并ORDER BY在其周围应用。
