mysql> select * from tb;
+----+------+-------+------+------+------+
| id | name | value | x | sta | pri |
+----+------+-------+------+------+------+
| 1 | A | 10 | NULL | 0 | NULL |
| 2 | B | 12 | NULL | 0 | NULL |
| 3 | C | 50 | NULL | 0 | NULL |
| 4 | ACD | 13 | NULL | 1 | NULL |
| 5 | AC | 18 | NULL | 0 | NULL |
| 6 | G | 10 | NULL | 0 | NULL |
+----+------+-------+------+------+------+
6 rows in set (0.19 sec)mysql>
mysql> update tb t inner join (
-> select a.id,a.name,(a.value-b.value)*10 as value,sum((c.value-b.value)*10) as pri
-> from tb a, (select value from tb where name='B') b,tb c
-> where instr(a.name,c.name)
-> group by a.id,a.name,a.value
-> ) x on t.id=x.id
-> set t.x=x.value,t.pri=x.pri;
Query OK, 6 rows affected (0.00 sec)
Rows matched: 6 Changed: 6 Warnings: 0mysql> select * from tb;
+----+------+-------+--------+------+--------+
| id | name | value | x | sta | pri |
+----+------+-------+--------+------+--------+
| 1 | A | 10 | -20.00 | 0 | -20.00 |
| 2 | B | 12 | 0.00 | 0 | 0.00 |
| 3 | C | 50 | 380.00 | 0 | 380.00 |
| 4 | ACD | 13 | 10.00 | 1 | 430.00 |
| 5 | AC | 18 | 60.00 | 0 | 420.00 |
| 6 | G | 10 | -20.00 | 0 | -20.00 |
+----+------+-------+--------+------+--------+
6 rows in set (0.00 sec)mysql>
现在想要实现的 更复杂一些 还可以搞么原数据样式
mysql> select * from tb;
+----+------+-------+------+------+------+
| id | name | value | x | sta | pri |
+----+------+-------+------+------+------+
| 1 | A | 10 | 20 | 0 | NULL |
| 2 | B | 12 | 30 | 0 | NULL |
| 3 | C | 15 | 40 | 0 | NULL |
| 4 | ACD | 13 | NULL | 1 | NULL |
| 5 | ACG | 18 | NULL | 1 | NULL |
| 6 | ACF | 10 | NULL | 1 | NULL |
| 7 | G | 10 | 10 | 0 | NULL |
+----+------+-------+------+------+------+更新后想要的结果:
mysql> select * from tb;
+----+------+-------+------+------+------+
| id | name | value | x | sta | pri |
+----+------+-------+------+------+------+
| 1 | A | 10 | 20 | 0 | NULL |
| 2 | B | 12 | 30 | 0 | NULL |
| 3 | C | 15 | 40 | 0 | NULL |
| 4 | ACD | 13 | 0 | 1 | 60 |
| 5 | ACG | 18 | 50 | 1 | 120 |
| 6 | ACF | 10 | -30 | 1 | 30 |
| 7 | G | 10 | 10 | 0 | NULL |
+----+------+-------+------+------+------+//以ACD的VALUE值为标准,计算name的长度是一样而且前2个字母相同的X的值进行更新,PRI值更新//PRI值为包含的关系,X值相加 ACD.pri=A.x+C.x+ACD.x
mysql>
DROP TABLE IF EXISTS `tb`;
CREATE TABLE `tb` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(50) default NULL,
`value` varchar(50) default NULL,
`x` float(11,2) default NULL,
`sta` char(2) default NULL,
`pri` float(11,2) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312;-- ----------------------------
-- Records of tb
-- ----------------------------
INSERT INTO `tb` VALUES ('1', 'A', '10', '20.00', '0', null);
INSERT INTO `tb` VALUES ('2', 'B', '12', '30.00', '0', null);
INSERT INTO `tb` VALUES ('3', 'C', '15', '40.00', '0', null);
INSERT INTO `tb` VALUES ('4', 'ACD', '13', null, '1', null);
INSERT INTO `tb` VALUES ('5', 'ACG', '18', null, '1', null);
INSERT INTO `tb` VALUES ('6', 'ACF', '10', null, '1', null);
INSERT INTO `tb` VALUES ('7', 'G', '10', '10.00', '0', null);
+----+------+-------+------+------+------+
| id | name | value | x | sta | pri |
+----+------+-------+------+------+------+
| 1 | A | 10 | NULL | 0 | NULL |
| 2 | B | 12 | NULL | 0 | NULL |
| 3 | C | 50 | NULL | 0 | NULL |
| 4 | ACD | 13 | NULL | 1 | NULL |
| 5 | AC | 18 | NULL | 0 | NULL |
| 6 | G | 10 | NULL | 0 | NULL |
+----+------+-------+------+------+------+
6 rows in set (0.19 sec)mysql>
mysql> update tb t inner join (
-> select a.id,a.name,(a.value-b.value)*10 as value,sum((c.value-b.value)*10) as pri
-> from tb a, (select value from tb where name='B') b,tb c
-> where instr(a.name,c.name)
-> group by a.id,a.name,a.value
-> ) x on t.id=x.id
-> set t.x=x.value,t.pri=x.pri;
Query OK, 6 rows affected (0.00 sec)
Rows matched: 6 Changed: 6 Warnings: 0mysql> select * from tb;
+----+------+-------+--------+------+--------+
| id | name | value | x | sta | pri |
+----+------+-------+--------+------+--------+
| 1 | A | 10 | -20.00 | 0 | -20.00 |
| 2 | B | 12 | 0.00 | 0 | 0.00 |
| 3 | C | 50 | 380.00 | 0 | 380.00 |
| 4 | ACD | 13 | 10.00 | 1 | 430.00 |
| 5 | AC | 18 | 60.00 | 0 | 420.00 |
| 6 | G | 10 | -20.00 | 0 | -20.00 |
+----+------+-------+--------+------+--------+
6 rows in set (0.00 sec)mysql>
现在想要实现的 更复杂一些 还可以搞么原数据样式
mysql> select * from tb;
+----+------+-------+------+------+------+
| id | name | value | x | sta | pri |
+----+------+-------+------+------+------+
| 1 | A | 10 | 20 | 0 | NULL |
| 2 | B | 12 | 30 | 0 | NULL |
| 3 | C | 15 | 40 | 0 | NULL |
| 4 | ACD | 13 | NULL | 1 | NULL |
| 5 | ACG | 18 | NULL | 1 | NULL |
| 6 | ACF | 10 | NULL | 1 | NULL |
| 7 | G | 10 | 10 | 0 | NULL |
+----+------+-------+------+------+------+更新后想要的结果:
mysql> select * from tb;
+----+------+-------+------+------+------+
| id | name | value | x | sta | pri |
+----+------+-------+------+------+------+
| 1 | A | 10 | 20 | 0 | NULL |
| 2 | B | 12 | 30 | 0 | NULL |
| 3 | C | 15 | 40 | 0 | NULL |
| 4 | ACD | 13 | 0 | 1 | 60 |
| 5 | ACG | 18 | 50 | 1 | 120 |
| 6 | ACF | 10 | -30 | 1 | 30 |
| 7 | G | 10 | 10 | 0 | NULL |
+----+------+-------+------+------+------+//以ACD的VALUE值为标准,计算name的长度是一样而且前2个字母相同的X的值进行更新,PRI值更新//PRI值为包含的关系,X值相加 ACD.pri=A.x+C.x+ACD.x
mysql>
DROP TABLE IF EXISTS `tb`;
CREATE TABLE `tb` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(50) default NULL,
`value` varchar(50) default NULL,
`x` float(11,2) default NULL,
`sta` char(2) default NULL,
`pri` float(11,2) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312;-- ----------------------------
-- Records of tb
-- ----------------------------
INSERT INTO `tb` VALUES ('1', 'A', '10', '20.00', '0', null);
INSERT INTO `tb` VALUES ('2', 'B', '12', '30.00', '0', null);
INSERT INTO `tb` VALUES ('3', 'C', '15', '40.00', '0', null);
INSERT INTO `tb` VALUES ('4', 'ACD', '13', null, '1', null);
INSERT INTO `tb` VALUES ('5', 'ACG', '18', null, '1', null);
INSERT INTO `tb` VALUES ('6', 'ACF', '10', null, '1', null);
INSERT INTO `tb` VALUES ('7', 'G', '10', '10.00', '0', null);
-> set tb.x=(tb.value-b.value)*10
-> where tb.sta=1;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0mysql> select * from tb;
+----+------+-------+--------+------+------+
| id | name | value | x | sta | pri |
+----+------+-------+--------+------+------+
| 1 | A | 10 | 20.00 | 0 | NULL |
| 2 | B | 12 | 30.00 | 0 | NULL |
| 3 | C | 15 | 40.00 | 0 | NULL |
| 4 | ACD | 13 | 0.00 | 1 | NULL |
| 5 | ACG | 18 | 50.00 | 1 | NULL |
| 6 | ACF | 10 | -30.00 | 1 | NULL |
| 7 | G | 10 | 10.00 | 0 | NULL |
+----+------+-------+--------+------+------+
7 rows in set (0.00 sec)mysql> update tb inner join (
-> select a.id,sum(b.x) as pri
-> from tb a,tb b
-> where instr(a.name,b.name)
-> group by a.id
-> ) t on tb.id=t.id
-> set tb.pri=t.pri
-> where tb.sta=1;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0mysql> select * from tb;
+----+------+-------+--------+------+--------+
| id | name | value | x | sta | pri |
+----+------+-------+--------+------+--------+
| 1 | A | 10 | 20.00 | 0 | NULL |
| 2 | B | 12 | 30.00 | 0 | NULL |
| 3 | C | 15 | 40.00 | 0 | NULL |
| 4 | ACD | 13 | 0.00 | 1 | 60.00 |
| 5 | ACG | 18 | 50.00 | 1 | 120.00 |
| 6 | ACF | 10 | -30.00 | 1 | 30.00 |
| 7 | G | 10 | 10.00 | 0 | NULL |
+----+------+-------+--------+------+--------+
7 rows in set (0.00 sec)mysql>
(SELECT a.name,a.value,SUM(b.x) AS newx,SUM(DISTINCT A.VALUE-(SELECT VALUE FROM TB WHERE NAME='ACD'))*10 AS newv
FROM TB A LEFT JOIN TB B ON INSTR(A.name,B.name)>0
WHERE A.sta=1
GROUP BY a.name,a.value) b1
ON a1.name=b1.name
SET a1.x=b1.newv,a1.pri=b1.newx+b1.newv;
SELECT * FROM tb
CREATE TABLE `tb` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) DEFAULT NULL,
`value` VARCHAR(50) DEFAULT NULL,
`x` FLOAT(11,2) DEFAULT NULL,
`sta` CHAR(2) DEFAULT NULL,
`pri` FLOAT(11,2) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=gb2312;-- ----------------------------
-- Records of tb
-- ----------------------------
INSERT INTO `tb` VALUES ('1', 'A', '10', '20.00', '0', NULL);
INSERT INTO `tb` VALUES ('2', 'B', '12', '30.00', '0', NULL);
INSERT INTO `tb` VALUES ('3', 'C', '15', '40.00', '0', NULL);
INSERT INTO `tb` VALUES ('4', 'ACD', '13', NULL, '1', NULL);
INSERT INTO `tb` VALUES ('5', 'ACG', '18', NULL, '1', NULL);
INSERT INTO `tb` VALUES ('6', 'ACF', '10', NULL, '1', NULL);
INSERT INTO `tb` VALUES ('7', 'G', '10', '10.00', '0', NULL);SELECT a.name,a.value,SUM(b.x) AS newx,SUM(DISTINCT A.VALUE-(SELECT VALUE FROM TB WHERE NAME='ACD'))*10 AS newv
FROM TB A LEFT JOIN TB B ON INSTR(A.name,B.name)>0
WHERE A.sta=1
GROUP BY a.name,a.value;UPDATE tb a1 INNER JOIN
(SELECT a.name,a.value,SUM(b.x) AS newx,SUM(DISTINCT A.VALUE-(SELECT VALUE FROM TB WHERE NAME='ACD'))*10 AS newv
FROM TB A LEFT JOIN TB B ON INSTR(A.name,B.name)>0
WHERE A.sta=1
GROUP BY a.name,a.value) b1
ON a1.name=b1.name
SET a1.x=b1.newv,a1.pri=b1.newx+b1.newv;
SELECT * FROM tb;mysql> SELECT * FROM tb;
+----+------+-------+--------+------+--------+
| id | name | value | x | sta | pri |
+----+------+-------+--------+------+--------+
| 1 | A | 10 | 20.00 | 0 | NULL |
| 2 | B | 12 | 30.00 | 0 | NULL |
| 3 | C | 15 | 40.00 | 0 | NULL |
| 4 | ACD | 13 | 0.00 | 1 | 60.00 |
| 5 | ACG | 18 | 50.00 | 1 | 120.00 |
| 6 | ACF | 10 | -30.00 | 1 | 30.00 |
| 7 | G | 10 | 10.00 | 0 | NULL |
+----+------+-------+--------+------+--------+
7 rows in set (0.00 sec)mysql>
UPDATE tb a1 INNER JOIN
(SELECT a.name,a.value,SUM(b.x) AS newx,SUM(DISTINCT A.VALUE-(SELECT VALUE FROM TB WHERE NAME='ACD'))*10 AS newv
FROM TB A LEFT JOIN TB B ON INSTR(A.name,B.name)>0
WHERE A.sta=1
GROUP BY a.name,a.value) b1
ON a1.name=b1.name
SET a1.x=b1.newv,a1.pri=IF(A1.X IS NULL,b1.newx+b1.newv,b1.newx);
SELECT * FROM tb;