What's the easiest way to combine a few columns of a table to set up variables for another query?
I want to avoid constantly repeating the same if then statements in my query
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
Instead I would rather set up one variable
CASE
WHEN team_id = away_team_id THEN away_score
WHEN team_id = home_team_id THEN home_score
END AS team_score
and then query it much more cleanly
SELECT team_score * 5 AS team_score_times_five, team_score - 5 AS team_score_minus_five FROM t1
You might also try 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
CTEs allow you to stack multiple queries that would be nested in ways that tend to be more readable, and more easily modified in the future (depending on the situation) if the business requirement changes.
This article has a great section entitled "Use Common Table Expressions for extremely readable SQL" that makes additional arguments for why you'd want to use a CTE.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments