3FZmFDyH

1CREATE DEFINER=`mysql`@`%` PROCEDURE `test`()
2	LANGUAGE SQL
3	NOT DETERMINISTIC
4	CONTAINS SQL
5	SQL SECURITY DEFINER
6	COMMENT ''
7BEGIN
8	-- склонируем таблицу что бы оставить в целостности основную таблицу
9	DROP TABLE IF EXISTS `clone_data`;
10	
11	SHOW CREATE TABLE `data`;
12	CREATE TABLE `clone_data` (
13	  `id` int(11) NOT NULL AUTO_INCREMENT,
14	  `card_number` varchar(20) DEFAULT NULL,
15	  `date` datetime NOT NULL,
16	  `volume` float NOT NULL,
17	  `service` varchar(100) NOT NULL,
18	  `address_id` int(11) DEFAULT NULL,
19	  PRIMARY KEY (`id`)
20	) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
21	
22	INSERT INTO `clone_data` (`id`, `card_number`, `date`, `volume`, `service`, `address_id`)
23   SELECT `id`, `card_number`, `date`, `volume`, `service`, `address_id` FROM `data`; -- заполняем данные из основной таблице
24
25	 SELECT COUNT(*) INTO @nrow FROM `clone_data` WHERE SIGN(volume)=1; 
26	 WHILE @nrow > 0 DO
27      SELECT volume, id, address_id INTO @x_volume, @x_id, @x_address_id FROM clone_data WHERE SIGN(volume)=1 LIMIT 1;
28      SELECT volume, id INTO @y_volume, @y_id FROM clone_data WHERE id = @x_id+1 AND address_id = @x_address_id LIMIT 1;
29      UPDATE clone_data SET volume = @xvolume + @yvolume WHERE id = @yid;
30      DELETE FROM clone_data WHERE id = @xid;
31		SET @nrow := @nrow -1; 
32    END WHILE;
33END