SET @a=119; SET @b=0; update awq ,(SELECT *,@a:=IF(@b=id,@a,@a+1) as id_up,@b:=IF(@b<>id,id,@b) as id_1 FROM awq ) a set awq.id=a.id_up where awq.id = a.id_1注意执行一次就要重置一次@a,@b
抱歉,我太笨了,我就一张表a,那awp是虾米东西?
假设AWQ为你的表名,自行修改,A为别名 SET @a=119; SET @b=0; update awq ,(SELECT *,@a:=IF(@b=id,@a,@a+1) as id_up,@b:=IF(@b<>id,id,@b) as id_1 FROM awq ) a set awq.id=a.id_up where awq.id = a.id_1
[email protected]>create table a(id int);
Query OK, 0 rows affected (0.00 sec)
[email protected]>insert into a values(1),(1),(3),(4);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
[email protected]>alter table a add column id1 int not null auto_increment primary key;
Query OK, 4 rows affected (0.03 sec)
Records: 4 Duplicates: 0 Warnings: [email protected]>select * from a;
+------+-----+
| id | id1 |
+------+-----+
| 1 | 1 |
| 1 | 2 |
| 3 | 3 |
| 4 | 4 |
+------+-----+
4 rows in set (0.00 sec)[email protected]>update a set id1=id1+120;
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4 Changed: 4 Warnings: [email protected]>select * from a;
+------+-----+
| id | id1 |
+------+-----+
| 1 | 121 |
| 1 | 122 |
| 3 | 123 |
| 4 | 124 |
+------+-----+
4 rows in set (0.00 sec)看不懂了再给我留言
1楼没看清楚..
SET @b=0;
SELECT *,@a:=IF(@b=id,@a,@a+1),@b:=IF(@b<>id,id,@b) FROM qaw
SET @b=0;
update awq ,(SELECT *,@a:=IF(@b=id,@a,@a+1) as id_up,@b:=IF(@b<>id,id,@b) as id_1 FROM awq
) a
set awq.id=a.id_up
where awq.id = a.id_1注意执行一次就要重置一次@a,@b
SET @a=119;
SET @b=0;
update awq ,(SELECT *,@a:=IF(@b=id,@a,@a+1) as id_up,@b:=IF(@b<>id,id,@b) as id_1 FROM awq
) a
set awq.id=a.id_up
where awq.id = a.id_1