项目编号=各项目独立流水号&各年不同.sql

很好的sql 文档,可能邦你成为sql 高手

--自动编号的例子.项目编号=各项目独立流水号/各年不同

--创建自定义函数,得到新的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期
--*/