不选择子句排序过程MySql的位置

Question

Table：ID, UserID, NumberOfHours, WeekCommencing

该过程为每个用户添加一个新行，当任何一个用户有12行时，需要对该用户删除最早的行并添加一个新行。否则就加一行。这为每个用户保留了过去12周的运行记录。

PROBLEM "select count“由于某种原因没有选择where子句？

    CREATE DEFINER PROCEDURE `Avghours`(IN `Userid` INT, IN `Hours` DECIMAL(5,2), IN `WCdate` DATE)

    BEGIN
    DECLARE NUM INT;
    SELECT COUNT(*)
    FROM avghours
    WHERE UserID = Userid
    INTO NUM;

    IF NUM >= 12 then
    DELETE FROM avghours WHERE UserID = Userid
    ORDER BY UserID LIMIT 1;
    END IF;
    INSERT INTO avghours(
    UserID
   ,NumberOfHours
    ,WeekCommencing)
    VALUES(
    Userid
   ,Hours
   ,WCdate);

   END 

Answer 1

请检查一下，你会明白重点的

   mysql> DELIMITER $$
mysql> CREATE PROCEDURE `sp_testyt`()
    -> BEGIN
    -> 
    -> DECLARE NUMM INT;
    -> SELECT count(*) FROM gg into NUMM;
    -> SELECT @NUMM;
    -> 
    -> END $$
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql> call sp_testyt();
+-------+
| @NUMM |
+-------+
| NULL  |
+-------+
1 row in set (0.00 sec)

mysql> DELIMITER $$
mysql> CREATE PROCEDURE `sp_testtt`()
    -> BEGIN
    -> DECLARE NUM INT;
    -> SELECT count(*) FROM gg into @NUM;
    -> SELECT @NUM;
    -> 
    -> END $$
Query OK, 0 rows affected (0.02 sec)

mysql> DELIMITER ;
mysql> CALL sp_testtt();
+------+
| @NUM |
+------+
|    4 |
+------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

更新删除查询

DELETE FROM avghours WHERE ID in (SELECT ID FROM avghours WHERE Userid=Userid ORDER BY ID LIMIT 1)