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

scott 发表于 Dev

斯科特

在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;

西尔奇·德克

您可以创建一个子查询，并在最外面的查询中拥有最终的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

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。