create table test
(
uid int(8)
)
insert into test (uid) select 120;
insert into test (uid) select 120;
insert into test (uid) select 120;
insert into test (uid) select 121;
insert into test (uid) select 122;
insert into test (uid) select 123;
insert into test (uid) select 125;
insert into test (uid) select 125;
insert into test (uid) select 126;
insert into test (uid) select 128;
insert into test (uid) select 130;
后面还有,而且其中也有漏的,不是连续的值
/*
得到这样的效果:
uid
120
120
120
121
122
123
125
125
126
128
130*//*
问的是如何通过update语句达到以下效果:
120
120
120
121
122
123
124
124
125
126
127
*/
(
uid int(8)
)
insert into test (uid) select 120;
insert into test (uid) select 120;
insert into test (uid) select 120;
insert into test (uid) select 121;
insert into test (uid) select 122;
insert into test (uid) select 123;
insert into test (uid) select 125;
insert into test (uid) select 125;
insert into test (uid) select 126;
insert into test (uid) select 128;
insert into test (uid) select 130;
后面还有,而且其中也有漏的,不是连续的值
/*
得到这样的效果:
uid
120
120
120
121
122
123
125
125
126
128
130*//*
问的是如何通过update语句达到以下效果:
120
120
120
121
122
123
124
124
125
126
127
*/
set b.uid=c.nn
Where b.uid=c.uid
或者
update test b inner join (Select uid,(select min(uid)+count(distinct uid)-1 From test where uid<=a.uid) as nn from test a) c
on b.uid=c.uid
set b.uid=c.nn;mysql> select * from test;
+------+
| uid |
+------+
| 120 |
| 120 |
| 120 |
| 121 |
| 122 |
| 123 |
| 125 |
| 125 |
| 126 |
| 128 |
| 130 |
+------+
11 rows in set (0.00 sec)mysql> update test b,(Select uid,(select min(uid)+count(distinct uid)-1 From test where uid<=a.uid) as nn from test a) c
-> set b.uid=c.nn
-> Where b.uid=c.uid;
Query OK, 5 rows affected (0.05 sec)
Rows matched: 11 Changed: 5 Warnings: 0mysql> select * from test;
+------+
| uid |
+------+
| 120 |
| 120 |
| 120 |
| 121 |
| 122 |
| 123 |
| 124 |
| 124 |
| 125 |
| 126 |
| 127 |
+------+
11 rows in set (0.00 sec)mysql>