MySQL 行为不正常

Question

我有一个问题:

SELECT I.Id
    , CAST(SUBSTRING_INDEX(GROUP_CONCAT(I.StatusId ORDER BY I.TransactionId DESC), ',', 1) AS UNSIGNED) AS StatusId
    , SUM(I.RefundAmount) AS RefundAmount
    FROM (
        SELECT I.Id
            , IT.Id AS TransactionId
            , IT.StatusId
            , IF(IT.TypeId = 2, IT.RefundAmount, 0) AS RefundAmount
            FROM Items I
            INNER JOIN ItemTransactions IT ON IT.ItemId = I.Id
            WHERE I.Id = someValue
    ) I
    GROUP BY I.Id
    HAVING StatusId = 1 AND RefundAmount = 0

其中 Items 表有交易记录存储在 ItemTransactions 表中。直到这次我一直在使用这种类型的查询并为我工作，having 子句出现了一些问题。

查询在SQL 编辑器 中有效，但在存储过程 中使用时无法正常工作。 (不要误会我的意思，我的大部分存储过程 都使用了这个查询)。逐行调试，发现having子句有问题。

作为临时修复，我将查询更改为:

SELECT I.Id
    , I.StatusId
    , I.RefundAmount
    FROM (
        SELECT I.Id
            , CAST(SUBSTRING_INDEX(GROUP_CONCAT(I.StatusId ORDER BY I.TransactionId DESC), ',', 1) AS UNSIGNED) AS StatusId
            , SUM(I.RefundAmount) AS RefundAmount
            FROM (
                SELECT I.Id
                    , IT.Id AS TransactionId
                    , IT.StatusId
                    , IF(IT.TypeId = 2, IT.RefundAmount, 0) AS RefundAmount
                    FROM Items I
                    INNER JOIN ItemTransactions IT ON IT.ItemId = I.Id
                    WHERE I.Id = someValue
            ) I
            GROUP BY I.Id
            --HAVING StatusId = 1 AND RefundAmount = 0
    ) I
    WHERE I.StatusId = 1 AND I.RefundAmount = 0

查询工作正常。但我想知道是否有人已经遇到过这个问题，并找到了解决办法。我正在使用 MySQL 5.0。

谢谢

Answer 1

WHERE 子句用于过滤数据上的通用属性和表达式。 HAVING 子句用于在执行分组后过滤数据，它的参数应该是 GROUP BY 子句的参数，或者包含聚合函数的表达式。在 WHERE、GROUP BY 或 HAVING 子句中使用列的别名也是非法的，但它确实适用于 ORDER BY 子句。

如您所见，一个选项是使用子查询，然后列引用将起作用。另一种方法是在 HAVING 子句中复制整个表达式:

SELECT I.Id,
    CAST(SUBSTRING_INDEX(GROUP_CONCAT(I.StatusId ORDER BY I.TransactionId DESC),
           ',', 1) AS UNSIGNED) AS StatusId,
    SUM(I.RefundAmount) AS RefundAmount
    FROM (
        SELECT I.Id
            , IT.Id AS TransactionId
            , IT.StatusId
            , IF(IT.TypeId = 2, IT.RefundAmount, 0) AS RefundAmount
            FROM Items I
            INNER JOIN ItemTransactions IT ON IT.ItemId = I.Id
            WHERE I.Id = someValue
    ) I
    GROUP BY I.Id
    HAVING
    CAST(SUBSTRING_INDEX(GROUP_CONCAT(I.StatusId ORDER BY I.TransactionId DESC),
         ',', 1) AS UNSIGNED) = 1
    AND SUM(I.RefundAmount) = 0;

---

编辑: 仔细观察导致了这个问题 Using column alias in WHERE clause of MySQL query produces an error和 MySQL documentation , 这概述了可以在 GROUP BY、HAVING 和 ORDER BY 子句中使用列别名，如果这些别名用反引号引起来，例如:

...
HAVING `StatusId` = 1 AND `RefundAmount` = 0