（求mysql高手）如何批量更新n条记录的某一个字段值的和=固定值！

[H_Gragon]

//例如： id   num
         1     0
         2     0
         3     0
例如我给定的固定值是6：求mysql语句更新上面3条记录（也可能是n条）num的和为6，无论3条num的值是（2,2,2）或（1,2,3）都行 只要它们的和是6就行~！

[vipper23]

按照LZ要求最简单的 update tb set num=id;

[H_Gragon]

引用 1 楼 vipper23 的回复:

按照LZ要求最简单的 update tb set num=id;

大哥，我这是凑巧和id一样了，万一我给的固定值是9呢！

[知道就是你]

使用存储过程实现，大体代码如下，稍作修改应该可以实现：

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

[知道就是你]

不好意思，“DECLARE done1,INT DEFAULT 0; ” 多了一个 “逗号”！

[H_Gragon]

引用 3 楼 JenMinZhang 的回复:

使用存储过程实现，大体代码如下，稍作修改应该可以实现：

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

看不太明白，我再理解一下哦！

[ACMAIN_CHM]

直接更新成 6,0,0 不就行了？ 第一条记录直接为总和，然后其它均为0

[thomasLand]

sum--->和 n--->总列数 要求的是每行的数值,转化为将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) 不过,显然按版主说的做就行了不需太过麻烦。

[thomasLand]

一个简单的实例如下: 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)

[H_Gragon]

谢谢各位的回答，结贴！

[知道就是你]

"直接更新成 6,0,0 不就行了？ 第一条记录直接为总和，然后其它均为0 "支持斑竹大人 ，简单方便 ！