做加减运算而不是乘法运

转载

mob604756f06ed8 2017-01-19 16:26:00

w取和集，避免循环中循环，先将行冗余，再删除行，即做加减运算而不是乘法运算。

w问题：怎么在声量声明中引入变量？即实现下中将wa,wb,we不作为参数传入？

 1 DROP PROCEDURE IF EXISTS w_subset;
 2 DELIMITER /w/
 3 CREATE PROCEDURE w_subset()
 4 BEGIN
 5   DECLARE done INT DEFAULT FALSE;
 6   DECLARE w_wchar VARCHAR(256);
 7   DECLARE wcur CURSOR FOR SELECT wchar FROM wb;
 8   DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
 9 
10   OPEN wcur;
11   read_loop: LOOP
12     FETCH wcur INTO w_wchar;
13     IF done THEN LEAVE read_loop;
14     END IF;
15                     SET @wtmp=CONCAT('INSERT INTO ','wz',' (wchar) VALUES ("',w_wchar,'")');
16                     PREPARE  stmt FROM @wtmp ;
17                     EXECUTE  stmt ;
18                     DROP PREPARE stmt;
19   END LOOP;
20   CLOSE wcur;
21 END/w/
22 DELIMITER;
23 CALL w_subset();
24 
25 
26 DROP PROCEDURE IF EXISTS w_unique;
27 DELIMITER /w/
28 CREATE PROCEDURE w_unique()
29 BEGIN
30   DECLARE done INT DEFAULT FALSE;
31   DECLARE w_wmax INT;
32   DECLARE w_group VARCHAR(256);
33   DECLARE wcur CURSOR FOR SELECT MAX(id),GROUP_CONCAT(id)  FROM wz GROUP BY wchar;
34   DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
35 
36   OPEN wcur;
37   read_loop: LOOP
38     FETCH wcur INTO w_wmax,w_group;
39     IF done THEN LEAVE read_loop;
40     END IF;
41         SET @wpk_in=CONCAT(' IN ( ',w_group,' )');
42         SET @wtmp = CONCAT('DELETE FROM wz WHERE id!=',w_wmax,' AND id ',@wpk_in,'');
43  
44         PREPARE  stmt FROM @wtmp ;
45         EXECUTE  stmt ;
46         DROP PREPARE stmt;
47   END LOOP;
48   CLOSE wcur;
49 
50 END/w/
51 DELIMITER;
52 CALL w_unique();
53 
54 
55 CREATE UNIQUE INDEX win_wchar ON wz(wchar) USING BTREE;

id wint wchar

1 101 101wa
2 102 102wa
3 103 103wa
4 104 104wa
5 105 105wa
6 106 106wa
7 107 107wa
8 108 108wa
9 109 109wa
10 110 110wa
11 111 111wa
12 112 112wa
13 113 113wa
14 114 114wa
15 115 115wa
16 116 116wa

1 1001 1001wb
2 1002 1002wb
3 1003 1003wb
4 1004 1004wb
5 1005 1005wb
6 1006 1006wb
7 1007 1007wb
8 1008 1008wb
9 1009 1009wb
10 1010 1010wb
11 1011 1011wb
12 1012 1012wb
13 1013 1013wb
14 1014 1014wb
15 1015 1015wb
16 1016 1016wb

1 10001 10001we
2 10002 10002we
3 10003 10003we
4 10004 10004we
5 10005 10005we
6 10006 10006we
7 10007 10007we
8 10008 10008we
9 10009 10009we
10 10010 10010we
11 10011 10011we
12 10012 10012we
13 10013 10013we
14 10014 10014we
15 10015 10015we
16 10016 10016we

 1 DROP PROCEDURE IF EXISTS w_batch_tabs;
 2 DELIMITER /w/
 3 CREATE PROCEDURE w_batch_tabs(w_tab  VARCHAR(36),w_incr INT)
 4 BEGIN
 5     SET @wtmp = CONCAT( 'UPDATE ',w_tab,' SET wint=id+',w_incr,', wchar=CONCAT(wint,"',w_tab,'")');
 6     PREPARE  stmt FROM @wtmp ;
 7     EXECUTE  stmt ;
 8     DROP PREPARE stmt;
 9 END/w/
10 DELIMITER;
11 CALL w_batch_tabs('wa',100);
12 CALL w_batch_tabs('wb',1000);
13 CALL w_batch_tabs('we',10000);

1 TRUNCATE wz;
2 INSERT INTO wz SELECT * FROM wa WHERE id>14;
3 INSERT INTO wz SELECT * FROM wb WHERE id<9;
4 INSERT INTO wz SELECT * FROM we WHERE id>10 AND id<15;
5 UPDATE wz SET wchar=CONCAT(wint,'wz');
6 INSERT INTO wz(wint,wchar) SELECT wint,wchar FROM wa WHERE id>14;
7 INSERT INTO wz(wint,wchar) SELECT wint,wchar FROM wb WHERE id<9;
8 INSERT INTO wz(wint,wchar) SELECT wint,wchar FROM we WHERE id>10 AND id<15;

15 115 115wz
16 116 116wz
1 1001 1001wz
2 1002 1002wz
3 1003 1003wz
4 1004 1004wz
5 1005 1005wz
6 1006 1006wz
7 1007 1007wz
8 1008 1008wz
11 10011 10011wz
12 10012 10012wz
13 10013 10013wz
14 10014 10014wz
17 115 115wa
18 116 116wa
19 1001 1001wb
20 1002 1002wb
21 1003 1003wb
22 1004 1004wb
23 1005 1005wb
24 1006 1006wb
25 1007 1007wb
26 1008 1008wb
27 10011 10011we
28 10012 10012we
29 10013 10013we
30 10014 10014we