MySQL存储过程说在select语句中未定义别名

I have the following statement, which produces this error. Apparently the alias "R" in the first line of the select statement is not declared, but it's right there at the end of the statement. Anyone have any ideas?

我有以下语句,它会产生此错误。显然,select语句的第一行中的别名“R”未声明,但它正好在语句的末尾。有人有主意吗?

#1327 - Undeclared variable: R

#1327 - 未申报的变量:R

    CREATE PROCEDURE `get_series_completion`(IN team1_id INT, IN team2_id INT, IN round_shortname VARCHAR(5))
BEGIN
    DECLARE num_games_total, num_games_played INT;

    SELECT COUNT(*) INTO num_games_played, R.games_per_series INTO num_games_total
    FROM (
        SELECT game_id
        FROM team_games
        WHERE team_id IN (team1_id, team2_id)
        GROUP BY game_id
            HAVING COUNT(*) = 2
            AND SUM(standing = 0) = 0
    ) AS S, rounds AS R ON R.round_shortname = round_shortname;

    SELECT num_games_played/num_games_total;
END$$

1 个解决方案

#1

You should select all columns before into clause, Your query should be:

您应该在into子句之前选择所有列,您的查询应该是:

SELECT COUNT(*), R.games_per_series INTO num_games_played, num_games_total
    FROM (
        SELECT game_id
        FROM team_games
        WHERE team_id IN (team1_id, team2_id)
        GROUP BY game_id
            HAVING COUNT(*) = 2
            AND SUM(standing = 0) = 0
    ) AS S 
    LEFT OUTER JOIN rounds AS R ON R.round_shortname = round_shortname;

or

SELECT COUNT(*), R.games_per_series INTO num_games_played, num_games_total
    FROM (
        SELECT game_id
        FROM team_games
        WHERE team_id IN (team1_id, team2_id)
        GROUP BY game_id
            HAVING COUNT(*) = 2
            AND SUM(standing = 0) = 0
    ) AS S, rounds AS R 
    WHERE R.round_shortname(+) = round_shortname; -- implicit left outer join