mysql - 如何在相关子查询中正确地做别名

Question

对于以下子查询:

;WITH results as (
    SELECT 'DAL' as team, 2010 as season, 7 as wins union
    SELECT 'DAL' as team, 2011 as season, 11 as wins union
    SELECT 'DAL' as team, 2012 as season, 11 as wins union
    SELECT 'NE' as team, 2012 as season, 15 as wins union
    SELECT 'DET' as team, 2010 as season, 6 as wins
)

SELECT team, wins, season,
    wins - (select max(wins) from results as r where r.season=results.season) difference_from_best_subquery
FROM results;

为结果设置别名的正确方法是什么，以便清楚地表明您想将“这个”值传递到结果中？以上工作，但我希望我可以做一些更具可读性的事情(对我自己)，例如:

SELET MAX(wins) FROM table WHERE wins=$this.wins

最好的方法是什么？

Answer 1

在 MySQL 8+ 上，我只会使用 MAX作为解析函数:

SELECT team, wins, season,
    MAX(wins) OVER (PARTITION BY season) - wins AS diff_from_best
FROM results;

避免相关子查询的另一种方法是对子查询使用连接方法，该方法可以找到每个季节的最大获胜次数:

SELECT
    r1.team,
    r1.wins,
    r1.season,
    r2.max_wins - r1.wins AS diff_from_best
FROM results r1
INNER JOIN
(
    SELECT season, MAX(wins) AS max_wins
    FROM results
    GROUP BY season
) r2
    ON r2.season = r1.season;