sql - Postgres 如何避免在 select 语句中重复 if then

Question

组合表的几列来为另一个查询设置变量的最简单方法是什么？

我想避免在查询中不断重复相同的 if then 语句

SELECT 
CASE
WHEN team_id = away_team_id THEN away_score * 5
WHEN team_id = home_team_id THEN home_score * 5
END AS team_score_times_five, 
CASE
WHEN team_id = away_team_id THEN away_score - 5
WHEN team_id = home_team_id THEN home_score - 5
END AS team_score_minus_five
FROM t1

相反，我宁愿设置一个变量

CASE
WHEN team_id = away_team_id THEN away_score
WHEN team_id = home_team_id THEN home_score
END AS team_score

然后更干净地查询它

SELECT team_score * 5 AS team_score_times_five, team_score - 5 AS team_score_minus_five FROM t1

Answer 1

你也可以试试 common table expressions :

WITH common_table_expression AS (
    SELECT 
        CASE
          WHEN team_id = away_team_id THEN away_score
          WHEN team_id = home_team_id THEN home_score
          END AS team_score
    FROM t1
)
SELECT 
      team_score * 5 AS team_score_times_five
    , team_score - 5 AS team_score_minus_five
FROM common_table_expression

CTE 允许您堆叠多个查询，这些查询将以更易读的方式嵌套，并且如果业务需求发生变化，将来更容易修改(视情况而定)。

This article有一个很棒的部分，标题为“为极其可读的 SQL 使用公用表表达式”，它为您为什么要使用 CTE 提出了额外的论据。