源表为:
ID NAME VALUE X sta pri
1 A 10 0 0 null
2 B 12 0 0 null
3 C 15 0 0 null
4 ACD 20 0 1 null
更新后为:ID NAME VALUE X sta pri
1 A 10 -20 0 null
2 B 12 0 0 null
3 C 15 30 0 null
4 ACD 20 80 1 -20+30+80//x的值是以B为标准 VALUE值计算出来的 例:A-->X值为:(10-12)*10
//pri的值是当sta为1时需要更新的值(恩,如果忽略这个条件更方便,就可以忽略,把这列全更新也无所谓)
//pri的值的计算方式:把NAME分割 取NAME列包含分割后的字符列表,把X值相加并加上自己的X值
//SQL还能实现吗
ID NAME VALUE X sta pri
1 A 10 0 0 null
2 B 12 0 0 null
3 C 15 0 0 null
4 ACD 20 0 1 null
更新后为:ID NAME VALUE X sta pri
1 A 10 -20 0 null
2 B 12 0 0 null
3 C 15 30 0 null
4 ACD 20 80 1 -20+30+80//x的值是以B为标准 VALUE值计算出来的 例:A-->X值为:(10-12)*10
//pri的值是当sta为1时需要更新的值(恩,如果忽略这个条件更方便,就可以忽略,把这列全更新也无所谓)
//pri的值的计算方式:把NAME分割 取NAME列包含分割后的字符列表,把X值相加并加上自己的X值
//SQL还能实现吗
这个你已经有办法了。//pri的值的计算方式:把NAME分割 取NAME列包含分割后的字符列表,把X值相加并加上自己的X值
无法理解。建议详细描述。 建议你列出你的表结构,并提供测试数据以及基于这些测试数据的所对应正确结果。
参考一下这个贴子的提问方式http://topic.csdn.net/u/20091130/20/8343ee6a-417c-4c2d-9415-fa46604a00cf.html
1. 你的 create table xxx .. 语句
2. 你的 insert into xxx ... 语句
3. 结果是什么样,(并给以简单的算法描述)
4. 你用的数据库名称和版本(经常有人在MS SQL server版问 MySQL)
这样想帮你的人可以直接搭建和你相同的环境,并在给出方案前进行测试,避免文字描述理解上的误差。
+------+------+-------+------+------+------+
| id | name | value | X | sta | pri |
+------+------+-------+------+------+------+
| 1 | A | 10 | -20 | 0 | NULL |
| 2 | B | 12 | 0 | 0 | NULL |
| 3 | C | 15 | 30 | 0 | NULL |
| 4 | ACD | 20 | 80 | 1 | 90 |
+------+------+-------+------+------+------+
4 rows in set (0.00 sec)mysql>
DROP TABLE IF EXISTS `tangyu477`;
CREATE TABLE `tangyu477` (
`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 tangyu477
-- ----------------------------
INSERT INTO `tangyu477` VALUES ('1', 'A', '10', null, '0', null);
INSERT INTO `tangyu477` VALUES ('2', 'B', '12', null, '0', null);
INSERT INTO `tangyu477` VALUES ('3', 'C', '15', null, '0', null);
INSERT INTO `tangyu477` VALUES ('4', 'ACD', '20', null, '1', null);想要的结果:
/* ID NAME VALUE X sta pri
---------------------------------------------
1 A 10 -20 0 null
2 B 12 0 0 null
3 C 15 30 0 null
4 ACD 20 80 1 90
*/
ON 1=1
LEFT JOIN (SELECT A.STA,SUM(B.X) AS MA FROM TTH4 A INNER JOIN TTH4 B ON INSTR(A.NAME,B.NAME) >0 WHERE A.sta=1) D
ON A.sta=D.STA
SET A.`X`=10*(A.`VALUE`-B.`VALUE`),A.PRI=D.MA;
SELECT * FROM TTH4;
语句看起来很轻送 只是有错勿
[Err] 1140 - Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
mysql> UPDATE tth4 a INNER JOIN (SELECT ID,`VALUE` FROM tth4 WHERE NAME='B') B
-> ON 1=1
-> LEFT JOIN (SELECT A.STA,SUM(B.X) AS MA FROM TTH4 A INNER JOIN TTH4 B ON I
NSTR(A.NAME,B.NAME) >0 WHERE A.sta=1) D
-> ON A.sta=D.STA
-> SET A.`X`=10*(A.`VALUE`-B.`VALUE`),A.PRI=D.MA;
Query OK, 0 rows affected (0.03 sec)
Rows matched: 4 Changed: 0 Warnings: 0mysql> SELECT * FROM TTH4;
+------+------+-------+------+------+------+
| ID | NAME | VALUE | X | sta | pri |
+------+------+-------+------+------+------+
| 1 | A | 10 | -20 | 0 | NULL |
| 2 | B | 12 | 0 | 0 | NULL |
| 3 | C | 15 | 30 | 0 | NULL |
| 4 | ACD | 20 | 80 | 1 | 90 |
+------+------+-------+------+------+------+
4 rows in set (0.00 sec)mysql>ORmysql> UPDATE tth4 a INNER JOIN (SELECT ID,`VALUE` FROM tth4 WHERE NAME='B') B
-> ON 1=1
-> LEFT JOIN (SELECT A.STA,SUM(B.X) AS MA FROM TTH4 A INNER JOIN TTH4 B ON I
NSTR(A.NAME,B.NAME) >0 WHERE A.sta=1 GROUP BY A.STA) D
-> ON A.sta=D.STA
-> SET A.`X`=10*(A.`VALUE`-B.`VALUE`),A.PRI=D.MA;
Query OK, 0 rows affected (0.01 sec)
Rows matched: 4 Changed: 0 Warnings: 0mysql> SELECT * FROM TTH4;
+------+------+-------+------+------+------+
| ID | NAME | VALUE | X | sta | pri |
+------+------+-------+------+------+------+
| 1 | A | 10 | -20 | 0 | NULL |
| 2 | B | 12 | 0 | 0 | NULL |
| 3 | C | 15 | 30 | 0 | NULL |
| 4 | ACD | 20 | 80 | 1 | 90 |
+------+------+-------+------+------+------+
4 rows in set (0.00 sec)mysql>
Query OK, 1 row affected (0.02 sec)
Rows matched: 4 Changed: 1 Warnings: 0mysql> select * from tb;
+------+------+-------+------+------+------+
| id | name | value | x | sta | pri |
+------+------+-------+------+------+------+
| 1 | A | 10 | -20 | 0 | NULL |
| 2 | B | 12 | 0 | 0 | NULL |
| 3 | C | 15 | 30 | 0 | NULL |
| 4 | ACD | 20 | 80 | 1 | 90 |
+------+------+-------+------+------+------+
4 rows in set (0.01 sec)
好像有问题 我新增了2条测试数据 就出问题了//执行前的表:
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', null, '0', null);
INSERT INTO `tb` VALUES ('2', 'B', '12', null, '0', null);
INSERT INTO `tb` VALUES ('3', 'C', '50', null, '0', null);
INSERT INTO `tb` VALUES ('4', 'ACD', '13', null, '1', null);
INSERT INTO `tb` VALUES ('5', 'AC', '18', null, '0', null);
INSERT INTO `tb` VALUES ('6', 'G', '10', null, '0', null);//执行后:
/* ID NAME VALUE X sta pri
---------------------------------------------
1 A 10 -20 0 null
2 B 12 0 0 null
3 C 15 30 0 null
4 ACD 20 80 1 410
5 AC 18 60 0 NULL
6 G 10 -20 0 NULL
*/ 不应是410 是-20+30+60+80
是的 要执行2次 第一次 只更新X列 再执行一次 就只更新PRI了
晕,你id=3那行value到底是15还是50?
还有最后id=6那行的X值不加吗?
ON 1=1
LEFT JOIN (
SELECT A.STA,SUM((b.value-(SELECT VALUE FROM tb WHERE NAME='B'))*10) AS NEWV FROM TB A INNER JOIN TB B
ON INSTR(A.name,B.name)>0
WHERE A.STA=1
GROUP BY A.STA ) D
ON A.sta=D.STA
SET A.`X`=10*(A.`VALUE`-B.`VALUE`),A.PRI=D.NEWV;
SELECT * FROM Tb;
Query OK, 6 rows affected (0.02 sec)
Rows matched: 6 Changed: 6 Warnings: 0mysql> select * from tb;
+------+------+-------+------+------+------+
| id | name | value | x | sta | pri |
+------+------+-------+------+------+------+
| 1 | A | 10 | -20 | 0 | NULL |
| 2 | B | 12 | 0 | 0 | NULL |
| 3 | C | 50 | 380 | 0 | NULL |
| 4 | ACD | 13 | 10 | 1 | 430 |
| 5 | AC | 18 | 60 | 0 | NULL |
| 6 | G | 10 | -20 | 0 | NULL |
+------+------+-------+------+------+------+
6 rows in set (0.00 sec)
pri的值 应该是600 (A C AC ACD ACDH的值加起来) 不是-12017楼可以了 但 不知道你是怎么把 A C AC ACD ACDH的值取得的? 用哪个关键字的?
ID NAME VALUE X sta pri
1 A 10 -20 0 null
2 B 12 0 0 null
3 C 15 30 0 null
4 ACD 20 80 1 -20+30+80//x的值是以B为标准 VALUE值计算出来的 例:A-->X值为:(10-12)*10
//pri的值是当sta为1时需要更新的值(恩,如果忽略这个条件更方便,就可以忽略,把这列全更新也无所谓)
//pri的值的计算方式:把NAME分割 取NAME列包含分割后的字符列表,把X值相加并加上自己的X值
//SQL还能实现吗
就是INSTRSELECT A.STA,SUM((b.value-(SELECT VALUE FROM tb WHERE NAME='B'))*10) AS NEWV FROM TB A INNER JOIN TB B
ON INSTR(A.name,B.name)>0
WHERE A.STA=1
GROUP BY A.STA
+----+------+-------+------+------+------+
| 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>
UPDATE tb a INNER JOIN (SELECT ID,`VALUE` FROM tb WHERE NAME='B') B
ON 1=1
LEFT JOIN (
SELECT A.STA,SUM((b.value-(SELECT VALUE FROM tb WHERE NAME='B'))*10) AS NEWV FROM TB A INNER JOIN TB B
ON INSTR(A.name,B.name)>0
WHERE A.STA=1
GROUP BY A.STA ) D
ON A.sta=D.STA
SET A.`X`=10*(A.`VALUE`-B.`VALUE`),A.PRI=D.NEWV;
SELECT * FROM Tb;
不知道在哪里加条件 例如加一个查询 ID>1 and ID<6 的所有记录进行上面的操做