我有一个类似的表
ID | 名称 |
---|---|
1 | MICHAEL |
2 | JORDAN |
5 | 唐纳德 |
7 | JAYCE |
8 | ROY |
11 | JOHN |
16 | DOE |
如果您的MySQL
版本支持窗口功能,则可以使用ROW_NUMBER
。
假设您有下表:
create table test(
ID int NOT NULL ,
NAME VARCHAR (25)
);
insert into test values
(1,'MICHAEL'),
(2,'JORDAN'),
(5,'DONALD'),
(7,'JAYCE'),
(8,'ROY'),
(11,'JOHN'),
(16,'DOE');
使用row_number将给出:
select ID,NAME, row_number() over(order by ID ASC ) as rownum
from test
结果:
ID NAME rownum 1 MICHAEL 1 2 JORDAN 2 5 DONALD 3 7 JAYCE 4 8 ROY 5 11 JOHN 6 16 DOE 7
现在,创建另一个表test2
。
create table test2(
ID int NOT NULL ,
NAME VARCHAR (25)
);
我们可以使用INSERT INTO SELECT
:
INSERT INTO test2( ID, NAME )
WITH cte AS (
select ID,NAME, row_number() over(order by ID ASC ) as rownum
from test t1
)
SELECT rownum,NAME
FROM cte;
select * from test2;
结果:
ID NAME 1 MICHAEL 2 JORDAN 3 DONALD 4 JAYCE 5 ROY 6 JOHN 7 DOE
参见fiddle