1. Standard-SQL: LEFT JOIN
a single row of values
可以使用该条件计算一行值(从而计算一次).然后,可以使用COALESCE()
为每列添加回退值.
对于多个值,这种语法变体更短,速度稍快——对于昂贵/冗长的情况尤其有趣:
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;
由于派生表x
由single行组成,因此无需进一步条件即可进行连接.
Explicit type casts在子查询中是必需的.我在示例中使用了text
(这是字符串文本的默认值).使用实际的数据类型.语法快捷方式value::type
特定于Postgres,标准SQL使用cast(value AS type)
.
如果条件不是TRUE
,则x
中的所有值都为空,COALESCE
生效.
Or,因为在您的特定情况下,所有候选值都来自表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;
这取决于联接条件和查询的其余部分.
2.特定于PostgreSQL的
2a.展开数组
如果各个列共享101,则可以在子查询中使用数组,并在外部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;
如果列不共享相同的数据类型,情况会变得更复杂.您可以将它们全部转换为text
(并可以 Select 在外部SELECT
中转换回),或者您可以...
2b.分解行类型
您可以使用自定义复合类型(行类型)来保存各种类型的值,并在外部SELECT
中对其进行*-扩展.假设我们有三列:text
、integer
和date
.要使用,请创建一个自定义复合类型:
CREATE TYPE my_type (t1 text, t2 int, t3 date);
Or如果现有表的类型匹配,则可以将表名用作复合类型.
Or如果您只需要temporarily类型,您可以创建一个TEMPORARY TABLE
,它在session期间注册一个临时类型:
CREATE TEMP TABLE my_type (t1 text, t2 int, t3 date);
你甚至可以花single transaction英镑:
CREATE TEMP TABLE my_type (t1 text, t2 int, t3 date) ON COMMIT DROP;
然后可以使用以下查询:
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;
或者甚至只是(和上面一样,更简单,更短,可能更不容易理解):
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>;
CASE
表达式按这种方式为每列计算一次.如果计算不是微不足道的,那么另一个带有子查询的变量将更快.