mysql - SQL SERVER通过字段之间的数学运算顺序

您好，我有一张带有MLB 2019结果的表，并尝试创建排名。我在MySQL中创建了代码，并且工作正常，但SQL Server中的相同代码在ORDER BY子句中有错误。问题是Wins /（Wins + Losses）操作，因为它说没有“ Wins”和“ Losses”列，我知道我可以在select语句中写Wins /（Wins + Losses）AS Ratio，但我不知道想要在“结果”中查看“输赢”旁边的“比率”列。任何想法？

消息207，第16级，州1，第53行
无效的列名“ Wins”。
消息207，第16级，州1，第53行
无效的列名“ Wins”。
消息207，第16级，州1，第53行
无效的列名“ Losses”。

Select a1.Team,a1.HomeWins+a2.AwayWins as Wins,a1.HomeLoses+a2.AwayLoses
as Losses
FROM
(select t0.Team, t0.HomeWins,t1.HomeLoses from (select home as
Team,count(home) as HomeWins from mlbreg
where homescore>awayscore
group by home) t0  INNER JOIN (select home as Team,count(home) as
HomeLoses from mlbreg
where homescore<awayscore
group by home) t1 on t0.Team=t1.Team) a1
INNER JOIN
(select t2.Team, t2.AwayWins,t3.AwayLoses from (select away as
Team,count(away) as AwayWins from mlbreg
where homescore<awayscore
group by away) t2  INNER JOIN (select away as Team,count(away) as
AwayLoses from mlbreg
where homescore>awayscore
group by away) t3 on t2.Team=t3.Team) a2 on a1.Team=a2.Team
order by (Wins/(Wins+Losses)) desc;

最佳答案

尝试这个 -

Select a1.Team,a1.HomeWins+a2.AwayWins as Wins,a1.HomeLoses+a2.AwayLoses
as Losses
FROM
(select t0.Team, t0.HomeWins,t1.HomeLoses from (select home as
Team,count(home) as HomeWins from mlbreg
where homescore>awayscore
group by home) t0  INNER JOIN (select home as Team,count(home) as
HomeLoses from mlbreg
where homescore<awayscore
group by home) t1 on t0.Team=t1.Team) a1
INNER JOIN
(select t2.Team, t2.AwayWins,t3.AwayLoses from (select away as
Team,count(away) as AwayWins from mlbreg
where homescore<awayscore
group by away) t2  INNER JOIN (select away as Team,count(away) as
AwayLoses from mlbreg
where homescore>awayscore
group by away) t3 on t2.Team=t3.Team) a2 on a1.Team=a2.Team
order by ((a1.HomeWins+a2.AwayWins)/((a1.HomeWins+a2.AwayWins)+(a1.HomeLoses+a2.AwayLoses))) desc;

关于mysql - SQL SERVER通过字段之间的数学运算顺序，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/56902895/