KSH YXDH ZYDH ZYH CJ TZCJ TDCJ ZYZT
08500101111511 3113 04 1 617.000 637.000 637.000 NULL
08500101111511 3113 10 2 617.000 637.000 637.000 NULL
08500101111511 3113 29 3 617.000 637.000 637.000 NULL
08500101111511 3501 21 7 617.000 637.000 637.000 NULL
08500101111511 3501 18 8 617.000 637.000 637.000 NULL
08500101111511 1141 02 9 617.000 637.000 637.000 NULL
字段ZYH这个中间断了。我想吧它变成按顺序的123456,789分别变成456怎么。SQL语句怎么去写!
谢谢
08500101111511 3113 04 1 617.000 637.000 637.000 NULL
08500101111511 3113 10 2 617.000 637.000 637.000 NULL
08500101111511 3113 29 3 617.000 637.000 637.000 NULL
08500101111511 3501 21 7 617.000 637.000 637.000 NULL
08500101111511 3501 18 8 617.000 637.000 637.000 NULL
08500101111511 1141 02 9 617.000 637.000 637.000 NULL
字段ZYH这个中间断了。我想吧它变成按顺序的123456,789分别变成456怎么。SQL语句怎么去写!
谢谢
08500101111511 3113 04 1 617.000 637.000 637.000 NULL
08500101111511 3113 10 2 617.000 637.000 637.000 NULL
08500101111511 3113 29 3 617.000 637.000 637.000 NULL
08500101111511 3501 21 7 617.000 637.000 637.000 NULL
08500101111511 3501 18 8 617.000 637.000 637.000 NULL
08500101111511 1141 02 9 617.000 637.000 637.000 NULL来个清晰点的图
update 不就可以了?
或者你重建一个id自增长列就可以了
2:不修改数据的情况,建议使用row_number() 函数,显示的数字是顺序的。
--如果ZYH是标识列,取消标识列。
alter table TB add NewID int identity(1,1)
go
alter table TB drop column ZYH
GO
sp_rename 'TB.newid','ZYH','COLUMN'
create table fan
(KSH varchar(20),
YXDH varchar(10),
ZYDH varchar(5),
ZYH int,
CJ decimal(5,2),
TZCJ decimal(5,2),
TDCJ decimal(5,2),
ZYZT varchar(10)
)
insert into fan
select '08500101111511', '3113', '04', 1, 617.000, 637.000, 637.000, null union all
select '08500101111511', '3113', '10', 2, 617.000, 637.000, 637.000, null union all
select '08500101111511', '3113', '29', 3, 617.000, 637.000, 637.000, null union all
select '08500101111511', '3501', '21', 7, 617.000, 637.000, 637.000, null union all
select '08500101111511', '3501', '18', 8, 617.000, 637.000, 637.000, null union all
select '08500101111511', '1141', '02', 9, 617.000, 637.000, 637.000, null
update a
set a.ZYH=(select count(1) from fan b where b.ZYH<=a.ZYH)
from fan aselect * from fan/*
KSH YXDH ZYDH ZYH CJ TZCJ TDCJ ZYZT
-------------------- ---------- ----- ----------- --------------------------------------- --------------------------------------- --------------------------------------- ----------
08500101111511 3113 04 1 617.00 637.00 637.00 NULL
08500101111511 3113 10 2 617.00 637.00 637.00 NULL
08500101111511 3113 29 3 617.00 637.00 637.00 NULL
08500101111511 3501 21 4 617.00 637.00 637.00 NULL
08500101111511 3501 18 5 617.00 637.00 637.00 NULL
08500101111511 1141 02 6 617.00 637.00 637.00 NULL(6 row(s) affected)
*/
忘了说了。ZHY是char(1)类型。执行下,结果没出来???
create table fan
(KSH varchar(20),
YXDH varchar(10),
ZYDH varchar(5),
ZYH char(1),
CJ decimal(5,2),
TZCJ decimal(5,2),
TDCJ decimal(5,2),
ZYZT varchar(10)
)
insert into fan
select '08500101111511', '3113', '04', '1', 617.000, 637.000, 637.000, null union all
select '08500101111511', '3113', '10', '2', 617.000, 637.000, 637.000, null union all
select '08500101111511', '3113', '29', '3', 617.000, 637.000, 637.000, null union all
select '08500101111511', '3501', '21', '7', 617.000, 637.000, 637.000, null union all
select '08500101111511', '3501', '18', '8', 617.000, 637.000, 637.000, null union all
select '08500101111511', '1141', '02', '9', 617.000, 637.000, 637.000, null
update a
set a.ZYH=(select rtrim(count(1)) from fan b where b.ZYH<=a.ZYH)
from fan aselect * from fan/*
KSH YXDH ZYDH ZYH CJ TZCJ TDCJ ZYZT
-------------------- ---------- ----- ---- --------------------------------------- --------------------------------------- --------------------------------------- ----------
08500101111511 3113 04 1 617.00 637.00 637.00 NULL
08500101111511 3113 10 2 617.00 637.00 637.00 NULL
08500101111511 3113 29 3 617.00 637.00 637.00 NULL
08500101111511 3501 21 4 617.00 637.00 637.00 NULL
08500101111511 3501 18 5 617.00 637.00 637.00 NULL
08500101111511 1141 02 6 617.00 637.00 637.00 NULL(6 row(s) affected)
*/
KSH YXDH ZYDH ZYH CJ TZCJ TDCJ ZYZT
08500101111044 1141 02 1 612.000 632.000 632.000 NULL
08500101111045 1103 16 1 651.000 651.000 651.000 NULL
08500101111046 5127 08 1 618.000 618.000 618.000 NULL
08500101111047 1101 06 1 650.000 670.000 670.000 NULL
08500101111048 4201 33 1 634.000 634.000 634.000 NULL
08500101111044 1141 13 2 612.000 632.000 632.000 NULL
08500101111045 1103 26 2 651.000 651.000 651.000 NULL
08500101111046 5127 09 2 618.000 618.000 618.000 NULL
08500101111047 1101 08 2 650.000 670.000 670.000 NULL
08500101111048 4201 34 2 634.000 634.000 634.000 NULL
08500101111044 1141 12 3 612.000 632.000 632.000 NULL
08500101111045 1103 30 3 651.000 651.000 651.000 NULL
08500101111046 5127 11 3 618.000 618.000 618.000 NULL
08500101111047 1101 07 3 650.000 670.000 670.000 NULL
08500101111048 4201 26 3 634.000 634.000 634.000 NULL
08500101111044 1141 10 4 612.000 632.000 632.000 NULL
08500101111045 1103 14 4 651.000 651.000 651.000 NULL
08500101111046 5127 21 4 618.000 618.000 618.000 NULL
08500101111047 1101 02 4 650.000 670.000 670.000 NULL
08500101111044 1141 07 5 612.000 632.000 632.000 NULL
08500101111045 1103 21 5 651.000 651.000 651.000 NULL
08500101111046 5127 20 5 618.000 618.000 618.000 NULL
08500101111044 1141 08 6 612.000 632.000 632.000 NULL
08500101111045 1103 27 6 651.000 651.000 651.000 NULL
08500101111046 5127 15 6 618.000 618.000 618.000 NULL
08500101111044 5122 06 7 612.000 632.000 632.000 NULL
08500101111045 5122 01 7 651.000 651.000 651.000 NULL
08500101111046 5122 24 7 618.000 618.000 618.000 NULL
08500101111047 3101 21 7 650.000 670.000 670.000 NULL
在根据KSH,order by 排序应该是可以的了
在根据KSH,order by 排序应该是可以的了出现这种错误!
消息 8152,级别 16,状态 14,第 1 行将截断字符串或二进制数据。
在根据KSH,order by 排序应该是可以的了
能不能通过构建个临时表,先把数据导入临时表,再把数据从临时表导入表中!
在根据KSH,order by 排序应该是可以的了出现这种错误!
消息 8152,级别 16,状态 14,第 1 行将截断字符串或二进制数据。这个应该是你的 ZYH列,长度不够导致的
create table fan(KSH varchar(20), YXDH varchar(10), ZYDH varchar(5), ZYH char(1), CJ decimal(5,2), TZCJ decimal(5,2), TDCJ decimal(5,2), ZYZT varchar(10))
insert into fan select '08500101111511', '3113', '04', '1', 617.000, 637.000, 637.000, null union all select '08500101111511', '3113', '10', '2', 617.000, 637.000, 637.000, null union all select '08500101111511', '3113', '29', '3', 617.000, 637.000, 637.000, null union all select '08500101111511', '3501', '21', '7', 617.000, 637.000, 637.000, null union all select '08500101111511', '3501', '18', '8', 617.000, 637.000, 637.000, null union all select '08500101111511', '1141', '02', '9', 617.000, 637.000, 637.000, null with cte as(
select px = row_number() over ( order by zyh ),zyh from fan
)update cte set zyh=px
select * from fan
写法最简单,而且最容易理解!