PostgreSQL如何避免SELECT重复条件判断及复用列计算变量

Great question! Repeating conditional logic like those duplicate CASE statements can clutter your query and make it way harder to update later (imagine having to tweak the condition in 5 places instead of 1). Let's walk through the best ways to reuse that logic, plus how to create reusable variables from column combinations.

Option 1: Use LATERAL Subqueries (My Go-To for Simple Reuse) #

PostgreSQL's LATERAL join lets you define a subquery that references columns from the main table—perfect for extracting repeated logic into a single place. You can calculate your base score once, then reuse it for all your calculations:

SELECT
  score_calc.base_score * 5 AS team_score_times_five,
  score_calc.base_score - 5 AS team_score_minus_five
FROM t1,
LATERAL (
  SELECT CASE
    WHEN team_id = away_team_id THEN away_score
    WHEN team_id = home_team_id THEN home_score
  END AS base_score
) AS score_calc;

The LATERAL subquery runs once per row from t1, computes base_score using your conditional logic, and then you can reference that value as many times as you want in the main query. This keeps your logic DRY (Don't Repeat Yourself) and easy to tweak down the line.

Option 2: Use a CTE (Common Table Expression) for More Complex Workflows #

If you need to reuse multiple derived values or plan to build on this data in subsequent queries, a CTE is a clean way to structure things. First, compute your base values in a temporary "view", then query that:

WITH team_score_data AS (
  SELECT
    -- Include any original columns you need, plus your derived base score
    team_id,
    away_team_id,
    home_team_id,
    CASE
      WHEN team_id = away_team_id THEN away_score
      WHEN team_id = home_team_id THEN home_score
    END AS base_score
  FROM t1
)
SELECT
  base_score * 5 AS team_score_times_five,
  base_score - 5 AS team_score_minus_five
FROM team_score_data;

CTEs are especially useful if you need to join this derived data with other tables later—they keep your main query focused on the final calculations instead of cluttering it with conditional logic.

Option 3: Nested Subquery (Simple, Straightforward) #

For quick one-off reuse, a basic nested subquery works too. Just calculate your base score in an inner query, then use it in the outer SELECT:

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

This is the simplest syntax if you don't need additional columns or more complex logic beyond reusing the base score.

Bonus: Reusing Multiple Derived Variables #

If you need to compute multiple values based on the same conditional logic (like team name plus score), just add more columns to your LATERAL subquery or CTE:

SELECT
  score_calc.base_score * 5 AS team_score_times_five,
  score_calc.base_score - 5 AS team_score_minus_five,
  score_calc.team_name AS current_team_name
FROM t1,
LATERAL (
  SELECT
    CASE WHEN team_id = away_team_id THEN away_score ELSE home_score END AS base_score,
    CASE WHEN team_id = away_team_id THEN away_team_name ELSE home_team_name END AS team_name
  ) AS score_calc;

Now you're reusing the same team_id check for both score and name—no duplicate conditionals to maintain!

内容的提问来源于stack exchange，提问作者user1071182