mysql - 为什么 MySQL 告诉我该列不存在？

Question

我有 3 个表:协会(ID，名称)种族(身份证，姓名)stats(id,date,associationId,typeId,quantity)

我的目标是获得所有协会的列表，以及所有协会的类型列表和数量，排序顺序首先是协会的总数，然后是每种类型。为此，我有这个查询:

SELECT association.name AS associationName,
        race.name AS raceName,
        SUM(report.quantity) AS quantity,
        subrequest.totalquantity as totalquantity
    FROM stats report
    JOIN association ON association.id = report.associationId
    JOIN race ON race.id = report.raceId
    JOIN (
        SELECT SUM(report.quantity) AS totalquantity,
            association.id AS subId
        FROM stats report
        JOIN association ON association.id = report.associationId
        WHERE date LIKE "2013-11-%"
        GROUP BY association.id
        ORDER BY totalquantity DESC
    ) subrequest ON subrequest.subId = association.id
    WHERE date LIKE "2013-11-%"
    GROUP BY association.id, race.id
        ORDER BY totalquantity DESC, quantity DESC

它工作得很好!但是现在，我只想为每个协会提供两个最大的类型。我试过这个:

set @num := 0, @association := '';
select associationName, raceName, quantity, totalquantity,
@num := if(@association = associationName, @num + 1, 1) as row_number,
        @association := associationName as dummy
FROM (
    SELECT association.name AS associationName,
        race.name AS raceName,
        SUM(report.quantity) AS quantity,
        subrequest.totalquantity as totalquantity
    FROM stats report
    JOIN association ON association.id = report.associationId
    JOIN race ON race.id = report.raceId
    JOIN (
        SELECT SUM(report.quantity) AS totalquantity,
            association.id AS subId
        FROM stats report
        JOIN association ON association.id = report.associationId
        WHERE date LIKE "2013-11-%"
        GROUP BY association.id
        ORDER BY totalquantity DESC
    ) subrequest ON subrequest.subId = association.id
    WHERE date LIKE "2013-11-%"
    GROUP BY association.id, race.id
    ORDER BY totalquantity DESC, quantity DESC
) x
WHERE row_number <= 2

有了这个，我有一个错误:#1054 - 'where 子句'中的未知列'row_number' 如果我删除 WHERE 子句，它工作正常并且我得到了预期的结果。我还有一个包含正确数字的列 row_number。

我必须这样做才能让它工作:

set @num := 0, @association := '';
select associationName, raceName, quantity, totalquantity
FROM (
    select associationName, raceName, quantity, totalquantity,
    @num := if(@association = associationName, @num + 1, 1) as row_number,
            @association := associationName as dummy
    FROM (
        SELECT association.name AS associationName,
            race.name AS raceName,
            SUM(report.quantity) AS quantity,
            subrequest.totalquantity as totalquantity
        FROM association_race_report report
        JOIN association ON association.id = report.associationId
        JOIN race ON race.id = report.raceId
        JOIN (
            SELECT SUM(report.quantity) AS totalquantity,
                association.id AS subId
            FROM association_race_report report
            JOIN association ON association.id = report.associationId
            WHERE date LIKE "2013-11-%"
            GROUP BY association.id
            ORDER BY totalquantity DESC
        ) subrequest ON subrequest.subId = association.id
        WHERE date LIKE "2013-11-%"
        GROUP BY association.id, race.id
        ORDER BY totalquantity DESC, quantity DESC
    ) x
)y
WHERE row_number <= 2

这很好用!但我不明白为什么我需要添加另一个级别的请求才能使 WHERE 子句起作用？添加无用的查询级别真的让我很烦。有没有办法让它工作？为什么 MySQL 会引发此错误？

谢谢!

Answer 1

MySQL 不喜欢列引用，因为它是在查询的选择部分定义的——它还不能用于连接和 where 子句。

我看到您从那以后找到的两个解决方案是用定义它的表达式替换别名，或者创建一个中间子查询以避免重写整个事情。 (查询优化器将正常删除中间查询。)