上午就很辛苦的将算法写好,在自己的思路和流程下一个一个数据库都在我的手下形成了编码,其实上午根本没有忙什么都在想怎么搞了,下午才真正的将它给弄好,下面讲解下我自己听哈子。
首先。我们将原来的表请出来一个并且在里面添加一个字段,然后进行自动的编写代号。好下面来写具体的内容。
首先在我的思路里面有要在不同的情况下执行不同的方法,所以我真正的用到了len函数。
其实len函数也很简单,我是这么写的
len
(
@item_clsno
)
也就是求出@item_clsno的变量的长度。
那么我们开始我们的具体的思路了,开始i先备份一张表
SELECT
*
INTO
dbo.table_zong1
FROM dbo.table_zong
ORDER BY item_clsno
然后我们就在这个表上操作咯
FROM dbo.table_zong
ORDER BY item_clsno
DECLARE
YOUBIAO
CURSOR
FOR
SELECT [ id ] , [ item_clsno ]
FROM dbo.table_zong1
open YOUBIAO
declare
@id bigint ,
@item_clsno varchar ( 510 )
fetch next from YOUBIAO
into @id , @item_clsno
if len ( @item_clsno ) = 3
begin
update dbo.table_zong1
set item_subno = @item_clsno + ' 0 '
where [ id ] = @id
end
else if len ( @item_clsno ) = 2
begin
update dbo.table_zong1
set item_subno = @item_clsno + ' 00 '
where [ id ] = @id
end
else if len ( @item_clsno ) = 4
update dbo.table_zong1
set item_subno = @item_clsno
where [ id ] = @id
while @@fetch_status = 0
begin
fetch next from YOUBIAO
into @id , @item_clsno
if len ( @item_clsno ) = 3
begin
update dbo.table_zong1
set item_subno = @item_clsno + ' 0 '
where [ id ] = @id
end
else if len ( @item_clsno ) = 2
begin
update dbo.table_zong1
set item_subno = @item_clsno + ' 00 '
where [ id ] = @id
end
else
begin
update dbo.table_zong1
set item_subno = @item_clsno
where [ id ] = @id
end
end
close YOUBIAO
SELECT [ id ] , [ item_clsno ]
FROM dbo.table_zong1
open YOUBIAO
declare
@id bigint ,
@item_clsno varchar ( 510 )
fetch next from YOUBIAO
into @id , @item_clsno
if len ( @item_clsno ) = 3
begin
update dbo.table_zong1
set item_subno = @item_clsno + ' 0 '
where [ id ] = @id
end
else if len ( @item_clsno ) = 2
begin
update dbo.table_zong1
set item_subno = @item_clsno + ' 00 '
where [ id ] = @id
end
else if len ( @item_clsno ) = 4
update dbo.table_zong1
set item_subno = @item_clsno
where [ id ] = @id
while @@fetch_status = 0
begin
fetch next from YOUBIAO
into @id , @item_clsno
if len ( @item_clsno ) = 3
begin
update dbo.table_zong1
set item_subno = @item_clsno + ' 0 '
where [ id ] = @id
end
else if len ( @item_clsno ) = 2
begin
update dbo.table_zong1
set item_subno = @item_clsno + ' 00 '
where [ id ] = @id
end
else
begin
update dbo.table_zong1
set item_subno = @item_clsno
where [ id ] = @id
end
end
close YOUBIAO
然后再执行
这个语句。问刚才改好的数据添加编码
DEALLOCATE
YOUBIAO
DECLARE YOUBIAO CURSOR FOR
SELECT [ id ] , [ item_subno ]
FROM dbo.table_zong1
close YOUBIAO
open YOUBIAO
declare
@id bigint ,
@item_subno varchar ( 510 ),
@bianliang int ,
@item_subno2 varchar ( 510 ),
@bianliangvarchar varchar ( 510 )
Fetch next from YOUBIAO
INTO @id , @item_subno
set @bianliang = 1
set @item_subno2 = @item_subno
set @bianliangvarchar = ' 000 ' + cast ( @bianliang as nvarchar ( 510 ))
UPDATE dbo.table_zong1
SET item_subno = @item_subno + @bianliangvarchar
WHERE [ id ] = @id
WHILE @@FETCH_STATUS = 0
BEGIN
Fetch next from YOUBIAO
INTO @id , @item_subno
if @item_subno2 = @item_subno
begin
set @bianliang = @bianliang + 1
if len ( @bianliang ) = 1
begin
set @bianliangvarchar = ' 000 ' + cast ( @bianliang as nvarchar ( 510 ))
UPDATE dbo.table_zong1
SET item_subno = @item_subno + @bianliangvarchar
WHERE [ id ] = @id
end
else if len ( @bianliang ) = 2
begin
set @bianliangvarchar = ' 00 ' + cast ( @bianliang as nvarchar ( 510 ))
UPDATE dbo.table_zong1
SET item_subno = @item_subno + @bianliangvarchar
WHERE [ id ] = @id
end
else if len ( @bianliang ) = 3
begin
set @bianliangvarchar = ' 0 ' + cast ( @bianliang as nvarchar ( 510 ))
UPDATE dbo.table_zong1
SET item_subno = @item_subno + @bianliangvarchar
WHERE [ id ] = @id
end
else
begin
set @bianliangvarchar = cast ( @bianliang as nvarchar ( 510 ))
UPDATE dbo.table_zong1
SET item_subno = @item_subno + @bianliangvarchar
WHERE [ id ] = @id
end
end
else
begin
set @item_subno2 = @item_subno
set @bianliang = 1
set @bianliangvarchar = ' 000 ' + cast ( @bianliang as nvarchar ( 510 ))
UPDATE dbo.table_zong1
SET item_subno = @item_subno + @bianliangvarchar
WHERE [ id ] = @id
end
END
CLOSE YOUBIAO
DECLARE YOUBIAO CURSOR FOR
SELECT [ id ] , [ item_subno ]
FROM dbo.table_zong1
close YOUBIAO
open YOUBIAO
declare
@id bigint ,
@item_subno varchar ( 510 ),
@bianliang int ,
@item_subno2 varchar ( 510 ),
@bianliangvarchar varchar ( 510 )
Fetch next from YOUBIAO
INTO @id , @item_subno
set @bianliang = 1
set @item_subno2 = @item_subno
set @bianliangvarchar = ' 000 ' + cast ( @bianliang as nvarchar ( 510 ))
UPDATE dbo.table_zong1
SET item_subno = @item_subno + @bianliangvarchar
WHERE [ id ] = @id
WHILE @@FETCH_STATUS = 0
BEGIN
Fetch next from YOUBIAO
INTO @id , @item_subno
if @item_subno2 = @item_subno
begin
set @bianliang = @bianliang + 1
if len ( @bianliang ) = 1
begin
set @bianliangvarchar = ' 000 ' + cast ( @bianliang as nvarchar ( 510 ))
UPDATE dbo.table_zong1
SET item_subno = @item_subno + @bianliangvarchar
WHERE [ id ] = @id
end
else if len ( @bianliang ) = 2
begin
set @bianliangvarchar = ' 00 ' + cast ( @bianliang as nvarchar ( 510 ))
UPDATE dbo.table_zong1
SET item_subno = @item_subno + @bianliangvarchar
WHERE [ id ] = @id
end
else if len ( @bianliang ) = 3
begin
set @bianliangvarchar = ' 0 ' + cast ( @bianliang as nvarchar ( 510 ))
UPDATE dbo.table_zong1
SET item_subno = @item_subno + @bianliangvarchar
WHERE [ id ] = @id
end
else
begin
set @bianliangvarchar = cast ( @bianliang as nvarchar ( 510 ))
UPDATE dbo.table_zong1
SET item_subno = @item_subno + @bianliangvarchar
WHERE [ id ] = @id
end
end
else
begin
set @item_subno2 = @item_subno
set @bianliang = 1
set @bianliangvarchar = ' 000 ' + cast ( @bianliang as nvarchar ( 510 ))
UPDATE dbo.table_zong1
SET item_subno = @item_subno + @bianliangvarchar
WHERE [ id ] = @id
end
END
CLOSE YOUBIAO
这样编码就算是完成了。
到最后经理说我的编码有问题,原来是8位到13位的编码没有取价格最低的,没办法重新做了一下子,感觉还可以就是要了点时间,因为6万条数据所花的时间太长了。