mysql - 错误代码 : 1111. 在存储过程中组函数的使用无效

Question

出现上述错误。

我尝试在 while 循环中将 wheres 切换为havings，但没有成功。我不知道处于 while 循环中是否会破坏事物或什么？

目标是使用 Equipment_ava 临时表中存储的数据对可用元素进行下注，并根据发票中元素是否缺货进行减去。

BEGIN
DECLARE s int(5);
DECLARE m int(5);
DECLARE o date;
DECLARE i date;
DECLARE a int(3);
DECLARE e int(3);

DROP TABLE IF EXISTS equip_out;
CREATE TEMPORARY TABLE equip_out
SELECT line_items.invoice_number,date_out,date_due_in,equipment_qty,line_items.equipment_id FROM invoices join line_items
group by Sequence;

DROP TABLE IF EXISTS equip_ava;
CREATE TEMPORARY TABLE equip_ava
SELECT equipment_id,equipment_total FROM equipment
GROUP BY equipment_id;

SET @s = 1;
SET @m = MAX(line_items.Sequence);
    WHILE @s <= @m DO
        SELECT date_out INTO @o FROM invoices join line_items
            WHERE Sequence=@s
            GROUP BY Sequence;

        SELECT date_due_in INTO @i FROM invoices join line_items
            WHERE Sequence=@s
            GROUP BY Sequence;

        IF daily BETWEEN @o AND @i THEN
            SELECT equipment_qty INTO @a FROM line_items
            WHERE Sequence=@s
            GROUP BY Sequence;

            SELECT equipment_id INTO @e FROM line_items
            WHERE Sequence=@s
            GROUP BY Sequence;

            UPDATE equip_ava
            SET equipment_total=equipment_total-@a
            WHERE equipment_id=@e;
        END IF;
        SET @s=@s+1;
    END WHILE;
    SELECT * FROM equip_ava;

END

Answer 1

您不能直接在存储过程(或任何与此相关的SET 语句)中使用聚合函数；它需要是 SELECT 的一部分。您需要将该行更改为

SET @m = (SELECT MAX(Sequence) FROM line_items);

或

SELECT MAX(Sequence) INTO @m FROM line_items;

请注意，您已经声明了很多变量，但实际上并未使用它们。 @s 是全局变量，s 是程序中的局部变量。