建表语句
CREATE TABLE IF NOT EXISTS `shuai` (
`field1` varchar(50) NOT NULL,
`field2` int(11) NOT NULL,
`field3` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;INSERT INTO `shuai` (`field1`, `field2`, `field3`) VALUES
('hello', 1, 0),
('hello', 2, 0),
('hello', 3, 0),
('hello', 4, 0),
('hello', 5, 0),
('hi', 1, 0),
('hi', 2, 0),
('hi', 3, 0),
('hi', 4, 0),
('hi', 5, 0);存储过程语句
DROP PROCEDURE check_app_version;
DELIMITER //
CREATE PROCEDURE check_app_version()
BEGIN
DECLARE field1 VARCHAR;
DECLARE field2 INT;
DECLARE apps CURSOR FOR SELECT `field1`,MAX(`field2`) FROM `shuai` GROUP BY `field1`;
OPEN apps;
REPEAT
FETCH apps INTO field1,field2;
UPDATE `shuai` SET `field3` = 1 WHERE `shuai`.`field1` = field1 AND `shuai`.`field2` =field2 AND `shuai`.`field3` =0 LIMIT 1 ;
CLOSE apps;
END ;
//
DELIMITER ;
CREATE TABLE IF NOT EXISTS `shuai` (
`field1` varchar(50) NOT NULL,
`field2` int(11) NOT NULL,
`field3` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;INSERT INTO `shuai` (`field1`, `field2`, `field3`) VALUES
('hello', 1, 0),
('hello', 2, 0),
('hello', 3, 0),
('hello', 4, 0),
('hello', 5, 0),
('hi', 1, 0),
('hi', 2, 0),
('hi', 3, 0),
('hi', 4, 0),
('hi', 5, 0);存储过程语句
DROP PROCEDURE check_app_version;
DELIMITER //
CREATE PROCEDURE check_app_version()
BEGIN
DECLARE field1 VARCHAR;
DECLARE field2 INT;
DECLARE apps CURSOR FOR SELECT `field1`,MAX(`field2`) FROM `shuai` GROUP BY `field1`;
OPEN apps;
REPEAT
FETCH apps INTO field1,field2;
UPDATE `shuai` SET `field3` = 1 WHERE `shuai`.`field1` = field1 AND `shuai`.`field2` =field2 AND `shuai`.`field3` =0 LIMIT 1 ;
CLOSE apps;
END ;
//
DELIMITER ;
UNTIL done END REPEAT;
DECLARE field2 INT;
DECLARE apps CURSOR FOR SELECT `field1`,MAX(`field2`) FR' at line 3
DECLARE field2 INT;
带上长度
BEGIN
DECLARE field1 VARCHAR(100);
DECLARE field2 INT;
DECLARE apps CURSOR FOR SELECT `field1`,MAX(`field2`) FROM `shuai` GROUP BY `field1`;
OPEN apps;
REPEAT
FETCH apps INTO field1,field2;
UPDATE `shuai` SET `field3` = 1
WHERE `shuai`.`field1` = field1
AND `shuai`.`field2` =field2
AND `shuai`.`field3` =0;
UNTIL no_more_departments
END REPEAT;
CLOSE apps;
END
DELIMITER //
CREATE PROCEDURE check_app_version()
BEGIN
DECLARE field1 VARCHAR(20);
DECLARE field2 INT;
DECLARE done INT DEFAULT 0;
DECLARE apps CURSOR FOR SELECT `field1`,MAX(`field2`) FROM `shuai` GROUP BY `field1`;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN apps;
FETCH apps INTO field1,field2;
WHILE done=0 DO
UPDATE `shuai` SET `field3` = 1 WHERE `shuai`.`field1` = field1 AND `shuai`.`field2` =field2 AND `shuai`.`field3` =0 LIMIT 1 ;
SET done = 0;
FETCH apps INTO field1,field2;
END WHILE;
CLOSE apps;
END ;
//
DELIMITER ;
UPDATE `shuai` A INNER JOIN (SELECT FIELD1,MAX(FIELD2) AS MA FROM `shuai` GROUP BY FIELD1) B ON
A.FIELD1=B.FIELD1 AND A.FIELD2=B.MA
SET A.`field3` = 1 ;
DELIMITER $$DROP PROCEDURE IF EXISTS `check_app_version`$$CREATE DEFINER=`root`@`localhost` PROCEDURE `check_app_version`()
BEGIN
DECLARE Afield1 VARCHAR(20);
DECLARE Afield2 INT;
DECLARE done INT DEFAULT 0;
DECLARE apps CURSOR FOR SELECT `field1`,MAX(`field2`) FROM `shuai` GROUP BY `field1`;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN apps;
FETCH apps INTO Afield1,Afield2;
WHILE done=0 DO
UPDATE `shuai` SET `field3` = 1 WHERE `shuai`.`field1` = Afield1 AND `shuai`.`field2` =Afield2 AND `shuai`.`field3` =0 LIMIT 1 ;
SET done = 0;
FETCH apps INTO Afield1,Afield2;
END WHILE;
CLOSE apps;
END$$DELIMITER ;