선택 쿼리에서 대/소문자 별칭을 사용하는 방법

선택 쿼리에서 대/소문자 별칭을 사용하는 방법

SQL에서 select 쿼리에서 대소문자 에일리어스를 사용하는 방법은 문제가 되는 것이 카운트입니다.예를 들어, 아래에 select 에일리어스가 다음과 같이 설정되어 있는 것을 알 수 있습니다.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

부울값을 수치로 취급하는 편리한 MySQL 확장자를 사용하여 쿼리를 단순화할 것을 제안합니다.1정말 그리고0false의 경우:

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

언급URL : https://stackoverflow.com/questions/65898742/how-can-i-use-case-alias-in-a-select-query