在Table表里面插入记录,每查插入一条记录 StudyId 也插入一条记录
规律是根据Parterid字段实现的 在相同的Parterid下 StudyId 是按照顺序插入的
要达到效果如下:
Table
Id Name Parterid StudyId
1 Unit1 100 1
2 Unit2 100 2
3 Unit3 101 1
4 Unit4 101 2
5 Unit5 101 3
6 Unit5 101 4
------解决方案--------------------
- SQL code
create function f_MaxStudyId(@Parterid int) returns intasbegin declare @re int select @re=id from( select id=max(StudyId) from tb where [email protected] ) a set @re=isnull(@re,0)+1 return @reendgocreate table tb(ID int identity(1,1) ,Name varchar(20),Parterid int,StudyId int)gocreate trigger tri on tbinstead of insertas declare @Parentid int,@id int select * into # from inserted order by Parterid update # set @id=case when [email protected] then @id+1 else dbo.f_MaxStudyId(Parterid) end, [email protected], @Parentid=Parterid insert into tb select Name,Parterid,StudyId from #goinsert into tb([Name],Parterid) values('Unit1',100)insert into tb([Name],Parterid) values('Unit2',100)insert into tb([Name],Parterid) values('Unit3',101)insert into tb([Name],Parterid) values('Unit4',101)insert into tb([Name],Parterid) values('Unit5',101)insert into tb([Name],Parterid) values('Unit5',101)select * from tbdrop function f_MaxStudyIddrop table tb/*ID Name Parterid StudyId ----------- -------------------- ----------- ----------- 1 Unit1 100 12 Unit2 100 23 Unit3 101 14 Unit4 101 25 Unit5 101 36 Unit5 101 4（所影响的行数为 6 行）*/
------解决方案--------------------
邹老大的例子.
- SQL code
--自动编号的例子.材料编号=类别编号+流水号--创建自定义函数,得到新的IDcreate function f_getid(@类别编号 varchar(3))returns intasbegin declare @re int select @re=right(id,4) from( select id=max(材料编号) from tb where [email protected] ) a set @re=isnull(@re,0)+1 return(@re)endgo--创建测试表create table tb(材料编号 varchar(7) primary key default '',类别编号 varchar(3),材料名称 varchar(10))go--创建触发器,自动生成材料编号create trigger t_insert on tbinstead of insertasselect * into #t from inserted order by 类别编号declare @类别编号 varchar(3),@id intupdate #t set @id=case when @类别编号=类别编号 then @id+1 else dbo.f_getid(类别编号) end ,材料编号=类别编号+right('0000'+cast(@id as varchar),4) ,@类别编号=类别编号insert into tb select * from #tgo--插入数据测试insert into tb(类别编号,材料名称)select '101','A材料'union all select '101','B材料'union all select '302','C材料'--显示结果select * from tb order by 材料编号go--删除测试环境drop table tbdrop function f_getid