mysql> select * from tb;
+----+------+-------+------+------+------+
| id | name | value | x | sta | pri |
+----+------+-------+------+------+------+
| 1 | A | 10 | 20 | 1 | NULL |
| 2 | B | 12 | 30 | 1 | NULL |
| 3 | C | 15 | 40 | 1 | NULL |
| 4 | ACD | 13 | NULL | 1 | NULL |
| 5 | ACG | 18 | NULL | 1 | NULL |
| 6 | ACF | 10 | NULL | 1 | NULL |
| 7 | G | 10 | 10 | 1 | 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 | NULL | 0 | NULL |
| 5 | ACG | 18 | NULL | 0 | NULL |
| 6 | ACF | 10 | NULL | 0 | NULL |
| 7 | G | 10 | 10 | 0 | NULL |
| 8 | A | 10 | 20 | 1 | NULL |
| 9 | B | 12 | 30 | 1 | NULL |
| 10 | C | 15 | 40 | 1 | NULL |
| 11 | ACD | 13 | NULL | 1 | NULL |
| 12 | ACG | 18 | NULL | 1 | NULL |
| 13 | ACF | 10 | NULL | 1 | NULL |
| 14 | G | 10 | 10 | 1 | NULL |
+----+------+-------+------+------+------+//要最简单的语句 别搞太复杂了 越简单越好
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', '1', null);
INSERT INTO `tb` VALUES ('2', 'B', '12', '30.00', '1', null);
INSERT INTO `tb` VALUES ('3', 'C', '15', '40.00', '1', 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', '1', null);
+----+------+-------+------+------+------+
| id | name | value | x | sta | pri |
+----+------+-------+------+------+------+
| 1 | A | 10 | 20 | 1 | NULL |
| 2 | B | 12 | 30 | 1 | NULL |
| 3 | C | 15 | 40 | 1 | NULL |
| 4 | ACD | 13 | NULL | 1 | NULL |
| 5 | ACG | 18 | NULL | 1 | NULL |
| 6 | ACF | 10 | NULL | 1 | NULL |
| 7 | G | 10 | 10 | 1 | 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 | NULL | 0 | NULL |
| 5 | ACG | 18 | NULL | 0 | NULL |
| 6 | ACF | 10 | NULL | 0 | NULL |
| 7 | G | 10 | 10 | 0 | NULL |
| 8 | A | 10 | 20 | 1 | NULL |
| 9 | B | 12 | 30 | 1 | NULL |
| 10 | C | 15 | 40 | 1 | NULL |
| 11 | ACD | 13 | NULL | 1 | NULL |
| 12 | ACG | 18 | NULL | 1 | NULL |
| 13 | ACF | 10 | NULL | 1 | NULL |
| 14 | G | 10 | 10 | 1 | NULL |
+----+------+-------+------+------+------+//要最简单的语句 别搞太复杂了 越简单越好
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', '1', null);
INSERT INTO `tb` VALUES ('2', 'B', '12', '30.00', '1', null);
INSERT INTO `tb` VALUES ('3', 'C', '15', '40.00', '1', 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', '1', null);
CREATE TABLE newtb AS
SELECT * FROM (
SELECT @num:=@num+1,a.name,a.value,a.x,0,a.pri FROM tb a
UNION
SELECT @num:=@num+1,a.name,a.value,a.x,1,a.pri FROM tb a) a1;
DELETE FROM tb;
INSERT INTO tb SELECT * FROM newtb;
SELECT * FROM tb
UPDATE tb SET sta=0;
INSERT INTO tb(NAME,VALUE,X,sta,pri) SELECT `NAME`,`VALUE`,X,1,pri FROM tb;
SELECT * FROM tb
insert into tb select null,`name`,`value`,x,1,pri from tb;
+----+------+-------+------+------+------+
| id | name | value | x | sta | pri |
+----+------+-------+------+------+------+
| 1 | A | 10 | 20 | 0 | NULL |
| 2 | B | 12 | 30 | 1 | NULL |
| 7 | G | 10 | 10 | 1 | NULL |
+----+------+-------+------+------+------+更新后想要的结果:
mysql> select * from tb;
+----+------+-------+------+------+------+
| id | name | value | x | sta | pri |
+----+------+-------+------+------+------+
| 1 | A | 10 | 20 | 0 | NULL |
| 2 | B | 12 | 30 | 0 | NULL |
| 3 | G | 10 | 10 | 0 | NULL |
| 4 | B | 12 | 30 | 1 | NULL |
| 5 | G | 10 | 10 | 1 | NULL |
+----+------+-------+------+------+------+//要最简单的语句 别搞太复杂了 越简单越好
CREATE TABLE NEWTT AS
SELECT *, @NUM:=@NUM+1 AS ID FROM (
SELECT NAME,VALUE,X,0 AS STA,pri FROM TB
UNION ALL
SELECT * FROM (
SELECT NAME,VALUE,X,1,pri FROM TB ORDER BY `ID` LIMIT 1,2) A) B;
DELETE FROM TB;
INSERT INTO TB SELECT ID,NAME,VALUE,X,STA,pri FROM NEWTT
有时候举例提供测试数据也是一种学问,如果是商业行为,完成你一楼的测试就已经从合同的角度完成了。虽然CSDN是免费的,但希望提问的时候还是多花一些时间,这样即节约别人的时间,也使自己的问题尽早得到正确的解答。
三句。
select max(id) into @x from tb;
insert into tb select null,name,value,x,sta,pri from tb where sta=1;
update tb set sta=0 where id<=@x;