求教个 mysql 的语句

可以做一个 procedure 来做这个事情。


====================================================================
DELIMITER;;

DROP PROCEDURE IF EXISTS `update_a_orderid`;
CREATE PROCEDURE update_a_orderid ()
BEGIN
DECLARE a_id, a_bookid, a_orderid, prev_bookid INT UNSIGNED;
DECLARE done INT DEFAULT FALSE;
DECLARE cur1 CURSOR FOR (SELECT ID, BOOKID from A ORDER BY BOOKID);
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

SET a_orderid=1;
SET prev_bookid=0;
OPEN cur1;
read_loop: LOOP
FETCH cur1 INTO a_id, a_bookid;
IF done THEN
LEAVE read_loop;
END IF;
IF prev_bookid = 0 OR a_bookid > prev_bookid THEN
SET a_orderid = 1;
ELSE
SET a_orderid = a_orderid + 1;
END IF;
UPDATE A set orderid=a_orderid where id=a_id;
END LOOP;

CLOSE cur1;
END
;;

DELIMITER ;

====================================================================

最后调用 call update_a_orderid();就可以了。