MySQL - 在第二个动态列中引用动态列

Question

我有一个这样的表:

user | test_score
  a  |  50
  b  |  60

我需要查询结果，其中有一列是测试分数的 1.5 倍，然后是与原始分数的差异。结果将是:

user | test_score | adjusted_test_score | test_score_diff
  a  |  50        |            75       |   25
  b  |  60        |            90       |   30

我试过的查询:

 SELECT
 user AS user,
 test_score AS test_score,
 (test_score * 1.5) AS adjusted_test_score,
 (adjusted_test_score - test_score) AS test_score_diff

返回“adjusted_test_score”列不存在的错误。

有没有办法在不进行连接的情况下引用此列？

Answer 1

尝试:

SELECT user, test_score, adjusted_test_score,
    (adjusted_test_score - test_score) AS test_score_diff
FROM (
    SELECT user, test_score, (test_score * 1.5) AS adjusted_test_score
    FROM source_table
)

或者你也可以这样做:

SELECT user, test_score, (test_score * 1.5) AS adjusted_test_score,
    ((test_score * 1.5) - test_score) AS test_score_diff
FROM source_table