使用mysql在另一个字段中进行拆分和求和的数据库更新查询 [英] Database update query for split and sum in another field using mysql

问题描述

我有一张桌子

test
ID       text       sum
-----------------------
1        1_2_3         0 

2        2_3_4_5       0

我想将此表更新为

ID       text       sum
------------------------
1        1_2_3        6

2        2_3_4_5      14

如何编写查询或函数/过程.

how to write the query or function/procedure.

推荐答案

解决此问题的最佳方法是使每个文本字段都成为 SQL 语句.

The best way to go about this is to make each text field an SQL statement.

首先，这里是示例数据

mysql> drop table if exists prabhu;
Query OK, 0 rows affected (0.27 sec)

mysql> create table prabhu
    -> (
    ->   id int not null auto_increment primary key,
    ->   text varchar(128),
    ->   sum int default 0
    -> );
Query OK, 0 rows affected (0.56 sec)

mysql> insert into prabhu (text) values ('1_2_3'),('2_3_4_5');
Query OK, 2 rows affected (0.09 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from prabhu;
+----+---------+------+
| id | text    | sum  |
+----+---------+------+
|  1 | 1_2_3   |    0 |
|  2 | 2_3_4_5 |    0 |
+----+---------+------+
2 rows in set (0.00 sec)

mysql>

这里是一个查询，让每一行产生一条SQL语句来更新sum列

Here is a query to make each row produce an SQL statement to update the sum column

mysql> SELECT CONCAT('UPDATE prabhu SET sum=',
    -> REPLACE(text,'_','+'),' WHERE id=',id,';') sqlstmt FROM prabhu;
+-------------------------------------------+
| sqlstmt                                   |
+-------------------------------------------+
| UPDATE prabhu SET sum=1+2+3 WHERE id=1;   |
| UPDATE prabhu SET sum=2+3+4+5 WHERE id=2; |
+-------------------------------------------+
2 rows in set (0.00 sec)

mysql>

现在，将查询的输出通过管道返回到 mysql 并执行每一行

Now, pipe the output of the query back into mysql and execute each line

C:\>mysql -Dtest -ANe"SELECT CONCAT('UPDATE prabhu SET sum=',REPLACE(text,'_','+'),' WHERE id=',id,';') sqlstmt FROM pra
bhu" | mysql -Dtest

C:\>mysql -Dtest -Ae"SELECT * FROM prabhu"
+----+---------+------+
| id | text    | sum  |
+----+---------+------+
|  1 | 1_2_3   |    6 |
|  2 | 2_3_4_5 |   14 |
+----+---------+------+

C:\>

这篇关于使用mysql在另一个字段中进行拆分和求和的数据库更新查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！