7,763
社区成员
发帖
与我相关
我的任务
分享
--创建一个楼主已经有的表
create table tb1(name char(2))
insert into tb1 select 'a'
insert into tb1 select 'b'
insert into tb1 select 'c'
insert into tb1 select 'd'
insert into tb1 select 'e'
insert into tb1 select 'f'
insert into tb1 select 'g'
insert into tb1 select 'h'
insert into tb1 select 'i'
insert into tb1 select 'j'
insert into tb1 select 'k'
insert into tb1 select 'l'
insert into tb1 select 'm'
insert into tb1 select 'n'
insert into tb1 select 'o'
insert into tb1 select 'p'
insert into tb1 select 'q'
insert into tb1 select 'r'
insert into tb1 select 's'
insert into tb1 select 't'
insert into tb1 select 'u'
insert into tb1 select 'v'
insert into tb1 select 'w'
--下面是楼主想要做的事情
alter table tb1 add col1 int,col2 int, col3 int identity(1,1)
update tb1 set col1=floor((col3-0.5)/5)+1
update tb1 set col2=col3-(col1-1)*5
alter table tb1 drop column col3
select * from tb1
/*
name col1 col2
---- ----------- -----------
a 1 1
b 1 2
c 1 3
d 1 4
e 1 5
f 2 1
g 2 2
h 2 3
i 2 4
j 2 5
k 3 1
l 3 2
m 3 3
n 3 4
o 3 5
p 4 1
q 4 2
r 4 3
s 4 4
t 4 5
u 5 1
v 5 2
w 5 3
(23 行受影响)
*/