如何在选择查询中使用用例别名

Question

在SQL中，我如何在选择查询中使用用例别名?有问题的是一个计数，例如，您将在下面看到选择别名是用as设置的，但我不能在选择查询的其余部分使用别名。使用MySQL .

下面的问题行是:(win / total) * 100 as win_percent

DECLARE @team TEXT;
SET @team = "myTeam";
SELECT 
@team team,
COUNT(CASE WHEN home = @team then 1 ELSE NULL END) as home,
COUNT(CASE WHEN away = @team then 1 ELSE NULL END) as away,
COUNT(CASE WHEN (away = @team or home = @team) then 1 ELSE NULL END) as total,
COUNT(CASE WHEN winner = @team and (home = @team or away = @team)  then 1 ELSE NULL END) as win,    
COUNT(CASE WHEN (home = @team or away = @team) and (winner <> @team and winner <> "0") then 1 ELSE NULL END) as lost,
COUNT(CASE WHEN winner = "0" and (home = @team or away = @team) then 1 ELSE NULL END) as tie,
(869 / 1928) * 100 as win_percent,
(win / total) * 100 as win_percent
from matches;

Answer 1

您可以创建一个子查询，并将最终的win_percent放在最外层的查询中。查询应该如下所示:

SELECT a.*,
(win / total) * 100 as win_percent
FROM (
SELECT 
@team team,
COUNT(CASE WHEN home = @team then 1 ELSE NULL END) as home,
COUNT(CASE WHEN away = @team then 1 ELSE NULL END) as away,
COUNT(CASE WHEN (away = @team or home = @team) then 1 ELSE NULL END) as total,
COUNT(CASE WHEN winner = @team and (home = @team or away = @team)  then 1 ELSE NULL END) as win,    
COUNT(CASE WHEN (home = @team or away = @team) and (winner <> @team and winner <> "0") then 1 ELSE NULL END) as lost,
COUNT(CASE WHEN winner = "0" and (home = @team or away = @team) then 1 ELSE NULL END) as tie,
(869 / 1928) * 100 as win_percent
from matches
) AS a

Answer 2

我建议简化您的查询，使用方便的MySQL扩展，将布尔值视为数字，1为真，0为假:

SELECT @team team,
SUM(home = @team) as home,
SUM(away = @team) as away,
SUM(@team IN (away, home))) as total,
SUM(winner = @team and @team IN (away, home)) as win,    
SUM(winner <> @team and winner <> 0 and @team IN (away, home)) as lost,    
SUM(winner = '0' and @team IN (away, home)) as tie,
(869 / 1928) * 100 as win_percent,
AVG(CASE WHEN @team IN (away, home) THEN winner = @team END) as win_percent