使用存储过程实现,大体代码如下,稍作修改应该可以实现: CREATE PROCEDURE ps_update_num( IN ps_num int(11) CHARACTER SET utf8)
BEGIN DECLARE done1,INT DEFAULT 0; DECLARE ps_id,ps_totals INT DEFAULT 0;DECLARE ps_totals_id VARCHAR(100) DEFAULT '0'; DECLARE cur1 cursor for select id table_name ; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done1 = 1; open cur1; repeat FETCH cur1 INTO ps_id; if not done1 then if( ps_id = ps_num ) then update table_name set num=ps_num; end if ; if( ps_id < ps_num) then set ps_totals += ps_id ; set ps_totals_id =concat(ps_totals_id,",",ps_id); if( ps_totals =ps_num) then update table_name set num=ps_num; end if ; if( ps_totals >ps_num) then set ps_totals =0 ; set ps_totals_id='0'; end if ; end if ; end IF;until done1 end REPEAT; CLOSE cur1; commit; END
一个简单的实例如下:DELIMITER ; DROP TABLE IF EXISTS `test`; CREATE TABLE IF NOT EXISTS `test` ( `id` INT, `value` INT ); ALTER TABLE `test` ADD CONSTRAINT PK_TEST PRIMARY KEY(`id`);INSERT INTO `test`(`id`,`value`) VALUES (1,1), (2,2), (3,3);DELIMITER // DROP FUNCTION IF EXISTS `generate_distribution_sum`; CREATE FUNCTION `generate_distribution_sum`(pt_value INT) RETURNS INT BEGIN DECLARE p_sum INT DEFAULT 0; DECLARE p_id INT DEFAULT 0; DECLARE p_value INT DEFAULT 0; DECLARE done INT DEFAULT 0; DECLARE rows INT DEFAULT 0; DECLARE row INT DEFAULT 0; DECLARE cur CURSOR FOR SELECT id,value FROM test; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1 ; SET p_sum = pt_value; SELECT COUNT(*) INTO rows FROM test; OPEN cur; REPEAT FETCH cur INTO p_id,p_value; SET row= row+1; IF (row < rows) THEN
IF p_sum = 0 THEN SET p_value= 0; UPDATE test SET value = p_value where id = p_id; ELSE SET p_value = FLOOR(p_sum *rand()); SET p_sum = p_sum -p_value; UPDATE test SET value = p_value where id = p_id; END IF; ELSE UPDATE test SET value = p_sum where id = p_id; END IF; UNTIL done END REPEAT; CLOSE cur; RETURN 0; END//SELECT generate_distribution_sum(6)// SELECT generate_distribution_sum(18)// SELECT * FROM test // DELIMITER ; ==============================mysql> SELECT generate_distribution_sum(6)// +------------------------------+ | generate_distribution_sum(6) | +------------------------------+ | 0 | +------------------------------+ 1 row in set (0.01 sec)mysql> SELECT * FROM test // +----+-------+ | id | value | +----+-------+ | 1 | 1 | | 2 | 3 | | 3 | 2 | +----+-------+ 3 rows in set (0.00 sec) mysql> SELECT generate_distribution_sum(18)// +-------------------------------+ | generate_distribution_sum(18) | +-------------------------------+ | 0 | +-------------------------------+ 1 row in set (0.00 sec)mysql> select * from test; -> // +----+-------+ | id | value | +----+-------+ | 1 | 12 | | 2 | 3 | | 3 | 3 | +----+-------+ 3 rows in set (0.00 sec)
update tb set num=id;
CREATE
PROCEDURE ps_update_num( IN ps_num int(11) CHARACTER SET utf8)
BEGIN
DECLARE done1,INT DEFAULT 0;
DECLARE ps_id,ps_totals INT DEFAULT 0;DECLARE ps_totals_id VARCHAR(100) DEFAULT '0';
DECLARE cur1 cursor for select id table_name ;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done1 = 1;
open cur1;
repeat
FETCH cur1 INTO ps_id;
if not done1 then
if( ps_id = ps_num ) then
update table_name set num=ps_num;
end if ;
if( ps_id < ps_num) then
set ps_totals += ps_id ;
set ps_totals_id =concat(ps_totals_id,",",ps_id);
if( ps_totals =ps_num) then
update table_name set num=ps_num;
end if ;
if( ps_totals >ps_num) then
set ps_totals =0 ;
set ps_totals_id='0';
end if ;
end if ;
end IF;until done1 end
REPEAT;
CLOSE cur1;
commit;
END
要求的是每行的数值,转化为将sum个小球分到rows个小箱子中去,求其中的任意一个序列。
记此序列为 row1,row2,row3,...rown(rowi>0)
f(sum,n)=f(sum-k,n-1)+f(k,1);k∈N+,k<=sum
f(k,1)=k
f(0,n)=0+f(0,n-1) 不过,显然按版主说的做就行了不需太过麻烦。
DROP TABLE IF EXISTS `test`;
CREATE TABLE IF NOT EXISTS `test`
(
`id` INT,
`value` INT
);
ALTER TABLE `test` ADD CONSTRAINT PK_TEST PRIMARY KEY(`id`);INSERT INTO `test`(`id`,`value`) VALUES
(1,1),
(2,2),
(3,3);DELIMITER //
DROP FUNCTION IF EXISTS `generate_distribution_sum`;
CREATE FUNCTION `generate_distribution_sum`(pt_value INT) RETURNS INT
BEGIN
DECLARE p_sum INT DEFAULT 0;
DECLARE p_id INT DEFAULT 0;
DECLARE p_value INT DEFAULT 0;
DECLARE done INT DEFAULT 0;
DECLARE rows INT DEFAULT 0;
DECLARE row INT DEFAULT 0;
DECLARE cur CURSOR FOR SELECT id,value FROM test;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1 ;
SET p_sum = pt_value;
SELECT COUNT(*) INTO rows FROM test;
OPEN cur;
REPEAT
FETCH cur INTO p_id,p_value;
SET row= row+1;
IF (row < rows) THEN
IF p_sum = 0 THEN
SET p_value= 0;
UPDATE test SET value = p_value where id = p_id;
ELSE
SET p_value = FLOOR(p_sum *rand());
SET p_sum = p_sum -p_value;
UPDATE test SET value = p_value where id = p_id;
END IF;
ELSE
UPDATE test SET value = p_sum where id = p_id;
END IF;
UNTIL done END REPEAT;
CLOSE cur;
RETURN 0;
END//SELECT generate_distribution_sum(6)//
SELECT generate_distribution_sum(18)//
SELECT * FROM test //
DELIMITER ;
==============================mysql> SELECT generate_distribution_sum(6)//
+------------------------------+
| generate_distribution_sum(6) |
+------------------------------+
| 0 |
+------------------------------+
1 row in set (0.01 sec)mysql> SELECT * FROM test //
+----+-------+
| id | value |
+----+-------+
| 1 | 1 |
| 2 | 3 |
| 3 | 2 |
+----+-------+
3 rows in set (0.00 sec)
mysql> SELECT generate_distribution_sum(18)//
+-------------------------------+
| generate_distribution_sum(18) |
+-------------------------------+
| 0 |
+-------------------------------+
1 row in set (0.00 sec)mysql> select * from test;
-> //
+----+-------+
| id | value |
+----+-------+
| 1 | 12 |
| 2 | 3 |
| 3 | 3 |
+----+-------+
3 rows in set (0.00 sec)