关闭

【数据库SQL】自动生成编号功能

97人阅读 评论(0) 收藏 举报
分类:
--功能1:自动生成8位数货号,放在备注字段里memo
use issyytv3 
go
declare @id int
declare @no varchar(20)
declare @item_no varchar(20)
select @id=0
declare cur_1 cursor for
select item_no from t_bd_item_info
open cur_1 
fetch cur_1 into @item_no
while @@fetch_status = 0
begin
select @id = @id +1
        select @no = right('00000000' + convert(varchar(10),@id),8)
update t_bd_item_info set memo=@no where item_no=@item_no
fetch cur_1 into @item_no
end
close cur_1
deallocate cur_1
 


--select memo,* from t_bd_item_info order by memo


--功能2:根据当前类别编号获取2位数+6位自动生成,放在备注字段里memo
declare @subno_len int
declare @item_no varchar(20)
declare @item_clsno varchar(20)
declare @item_oldclsno varchar(20)
select @subno_len = 1 
declare cur_row cursor for
select item_no,substring(item_clsno,1,2) from t_bd_item_info order  by  item_clsno
open cur_row
fetch cur_row into @item_no,@item_clsno
while @@fetch_status=0
begin
if (@item_clsno<>@item_oldclsno)
begin
 set @subno_len=1
   end 
update t_bd_item_info set memo = rtrim(@item_clsno)+right('000000' + convert(varchar(8),@subno_len),6 ) 
where item_no = @item_no
select @item_oldclsno=@item_clsno
fetch cur_row into @item_no,@item_clsno 
select @subno_len = @subno_len + 1 
end
close cur_row 
deallocate cur_row
go


--select memo,* from t_bd_item_info order by memo
0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:19572次
    • 积分:1124
    • 等级:
    • 排名:千里之外
    • 原创:98篇
    • 转载:1篇
    • 译文:0篇
    • 评论:0条