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

时间：2022-02-12 15:06:57

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

10 个解决方案

#1

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

#2

引用 1 楼 vipper23 的回复:
按照LZ要求最简单的
update tb set num=id;

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

#3

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


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

#4

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

#5

引用 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

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

#6

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

#7

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) 

不过,显然按版主说的做就行了不需太过麻烦。  

#8

一个简单的实例如下:

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)

#9

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

#10

谢谢各位的回答，结贴！

#1

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

#2

引用 1 楼 vipper23 的回复:
按照LZ要求最简单的
update tb set num=id;

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

#3

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


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

#4

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

#5

引用 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

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

#6

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

#7

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) 

不过,显然按版主说的做就行了不需太过麻烦。  

#8

一个简单的实例如下:

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)

#9

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

#10

谢谢各位的回答，结贴！
标签：

相关文章