74.项目编号=各项目独立流水号/各年不同

小灰灰 2024-02-19 18:02 38阅读 0赞

--自动编号的例子.项目编号=各项目独立流水号/各年不同
    
    --创建自定义函数,得到新的ID
    create function f_getid(
    @年份 char(4),
    @项目名称 varchar(10)
    )returns int
    as
    begin
    	declare @re int
    
    	select @re=case when id is null then 1 else cast(@re as int)+1 end
    	from(
    		select id=max(项目编号) from 表 where 年份=@年份 and 项目名称=@项目名称
    	) a
    	return(@re)
    end
    go
    
    --创建测试表
    create table 表(年份 char(4),项目编号 char(4)
    	,项目名称 varchar(10),项目内容 varchar(100))
    go
    
    --创建触发器,自动生成项目编号
    create trigger t_insert on 表
    instead of insert
    as
    select * into #t from inserted order by 年份,项目名称
    declare @年份 char(4),@项目名称 varchar(10),@id int
    
    update #t set @id=case when @年份=年份 and @项目名称=项目名称
    		then @id+1 else dbo.f_getid(年份,项目名称) end
    	,项目编号=right('0000'+cast(@id as varchar),4)
    	,@年份=年份,@项目名称=项目名称
    insert into 表 select * from #t
    go
    
    --创建触发器,在删除/修改时,重新排序项目编号
    create trigger t_delete_update on 表
    after delete,update
    as
    declare @年份 char(4),@项目名称 varchar(10),@id int
    
    declare tb cursor local for
    	select 年份,项目名称 from deleted 
    	union
    	select 年份,项目名称 from inserted
    open tb
    fetch next from tb into @年份,@项目名称
    while @@fetch_status=0
    begin
    	set @id=0
    	update 表 set @id=@id+1
    		,项目编号=right('0000'+cast(@id as varchar),4)
    	where 年份=@年份 and 项目名称=@项目名称
    	fetch next from tb into @年份,@项目名称
    end
    close tb
    deallocate tb
    go
    
    --插入数据测试
    insert into 表(年份,项目名称,项目内容)
    select '2003','A项目','A-1期'
    union all select '2003','A项目','A-2期'
    union all select '2004','A项目','A-3期'
    union all select '2003','B项目','B-1期'
    union all select '2004','B项目','B-11期'
    union all select '2003','A项目','A-12期'
    union all select '2004','A项目','A-12期'
    union all select '2003','B项目','B-12期'
    union all select '2004','B项目','B-13期'
    
    --显示结果
    select * from 表 order by 年份,项目名称
    go
    
    --更新测试
    update 表 set 年份=2003 where 项目编号='0001' and 项目名称='A项目'
    
    --显示结果
    select * from 表 order by 年份,项目名称
    go
    
    --删除部分记录
    delete from 表 
    where (年份=2003 and 项目编号='0002') or (年份=2004 and 项目编号='0001')
    --显示结果
    select * from 表 order by 年份,项目名称
    
    go
    --删除测试环境
    drop table 表
    drop function f_getid
    
    /*--测试结果
    
    1.插入时,自动编号的效果
    
    年份   项目编号 项目名称       项目内容  
    ---- ---- ---------- ------------------
    2003 0001 A项目        A-1期
    2003 0002 A项目        A-2期
    2003 0003 A项目        A-12期
    2003 0001 B项目        B-12期
    2003 0002 B项目        B-1期
    2004 0001 A项目        A-3期
    2004 0002 A项目        A-12期
    2004 0001 B项目        B-13期
    2004 0002 B项目        B-11期
    
    
    2.修改时,自动编号的效果
    
    年份 项目编号 项目名称       项目内容  
    ---- ---- ---------- ------------------
    2003 0001 A项目        A-1期
    2003 0002 A项目        A-2期
    2003 0003 A项目        A-12期
    2003 0004 A项目        A-3期
    2003 0001 B项目        B-12期
    2003 0002 B项目        B-1期
    2004 0001 A项目        A-12期
    2004 0001 B项目        B-13期
    2004 0002 B项目        B-11期
    
    
    3.删除时,自动编号的效果
    
    年份   项目编号 项目名称       项目内容 
    ---- ---- ---------- -----------------
    2003 0001 A项目        A-1期
    2003 0002 A项目        A-12期
    2003 0003 A项目        A-3期
    2003 0001 B项目        B-12期
    2004 0001 B项目        B-11期
    --*/