由于昨天搬家,经理感觉有点对不住了,呵呵,其实 是我自己对不住了,
上午就很辛苦的将算法写好,在自己的思路和流程下一个一个数据库都在我的手下形成了编码,其实上午根本没有忙什么都在想怎么搞了,下午才真正的将它给弄好,下面讲解下我自己听哈子。
首先。我们将原来的表请出来一个并且在里面添加一个字段,然后进行自动的编写代号。好下面来写具体的内容。
首先在我的思路里面有要在不同的情况下执行不同的方法,所以我真正的用到了len函数。
其实len函数也很简单,我是这么写的
len
(
@item_clsno
)
也就是求出@item_clsno的变量的长度。
那么我们开始我们的具体的思路了,开始i先备份一张表
SELECT
*
INTO
dbo.table_zong1
FROM
dbo.table_zong
ORDER
BY
item_clsno
然后我们就在这个表上操作咯
DECLARE
YOUBIAO
CURSOR
FOR
SELECT
[
id
]
,
[
item_clsno
]
FROM
dbo.table_zong1
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
open
YOUBIAO
declare
@id
bigint
,
@item_clsno
varchar
(
510
)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
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
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
close
YOUBIAO
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
然后再执行
这个语句。问刚才改好的数据添加编码
DEALLOCATE
YOUBIAO
DECLARE
YOUBIAO
CURSOR
FOR
SELECT
[
id
]
,
[
item_subno
]
FROM
dbo.table_zong1
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
close
YOUBIAO
open
YOUBIAO
declare
@id
bigint
,
@item_subno
varchar
(
510
),
@bianliang
int
,
@item_subno2
varchar
(
510
),
@bianliangvarchar
varchar
(
510
)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
Fetch
next
from
YOUBIAO
INTO
@id
,
@item_subno
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
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
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
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万条数据所花的时间太长了。