一、现在有两张表~
表TABLE_1字段如下
DWID int
name varchar
BH_1 varchar
BH_2 varchar表TABLE_2字段如下
ID int
DWID int //表1ID
Number varchar //这个编号字段要让它自动获取.详细如下说明.
px bit
name varchar二、自动编号要求
当往表TABLE_2插入数据时
如果px字段的值为0 那么表TABLE_2的Number字段的编号为 表TABLE_1 BH_1字段的值+日期+(增值)+"F"
如果px字段的值为1 那么表TABLE_2的Number字段的编号为 日期+表TABLE_1 BH_2字段的值+(增值)三、希望得到的数据如下
表TABLE1:
DWID name BH_1 BH_2
1 AAA A 001
2 BBB B 002
3 CCC C 003
4 DDD D 004表TABLE2:ID DWID Number px name
1 1 A090001F 0 abcd
2 1 A090002F 0 abcd
3 2 B090001F 0 abcd
4 2 B090002F 0 abcd
5 3 C090001F 0 abcd
6 1 20090010001 1 abcd
7 1 20090010002 1 abcd
8 4 20090040001 1 abcd
9 3 20090030001 1 abcd
表TABLE_1字段如下
DWID int
name varchar
BH_1 varchar
BH_2 varchar表TABLE_2字段如下
ID int
DWID int //表1ID
Number varchar //这个编号字段要让它自动获取.详细如下说明.
px bit
name varchar二、自动编号要求
当往表TABLE_2插入数据时
如果px字段的值为0 那么表TABLE_2的Number字段的编号为 表TABLE_1 BH_1字段的值+日期+(增值)+"F"
如果px字段的值为1 那么表TABLE_2的Number字段的编号为 日期+表TABLE_1 BH_2字段的值+(增值)三、希望得到的数据如下
表TABLE1:
DWID name BH_1 BH_2
1 AAA A 001
2 BBB B 002
3 CCC C 003
4 DDD D 004表TABLE2:ID DWID Number px name
1 1 A090001F 0 abcd
2 1 A090002F 0 abcd
3 2 B090001F 0 abcd
4 2 B090002F 0 abcd
5 3 C090001F 0 abcd
6 1 20090010001 1 abcd
7 1 20090010002 1 abcd
8 4 20090040001 1 abcd
9 3 20090030001 1 abcd
if object_id('tb') is not null drop table tb
drop function dbo.FC_Next
create function dbo.FC_Next()
returns char(8)
as
begin
return (select 'BH'+right(1000001+isnull(right(max(BH),6),0),6) from tb)
end
create table tb
(
BH char(8) primary key default dbo.FC_Next(),
col int
)
select * from tb
begin tran
insert into tb (col) values (1)
insert into tb (col) values (2)
insert into tb(BH,col) values (dbo.FC_Next(),14)
commit tran
select * from tb
--(1)插入标识列
create table #tt
(
id int identity(1,1),
col int
)
insert into #tt select 1
union all select 2
union all select 3
select * from #tt
delete from #tt where id=2set identity_insert #tt on
insert into #tt(id,col) select 2,2 from #tt where
id=3
update #tt set id=3 where id=2
--(2)@@identity,scope_identiuty()之间的区别
/*
对于@@identity而言,它返回的是当前会话中任何作用域内的最后插入的一个标识值
对于scope_identity()而言,它返回的是当前作用域内插入的标识值
*/
create table t1
(
id int identity(1,1),
col int
)
create table t2
(
id int identity(100,1),
col int
)
create trigger tg_t1 on t1
for insert
as
insert into t2 select col from inserted
go
insert into t1 select 2
select @@identity,scope_identity()--(3)RowGuidCol
/*
如果应用程序需要生成整个数据库或者世界各地所有网络计算机的所有数据库中均为唯一标识符列
就使用RowGuidCol列
*/
create table #tr
(
id uniqueidentifier rowguidcol primary key default newid(),
col uniqueidentifier
)
insert into #tr (col)
select newid()
union all select newid()
select rowguidcol,* from #tr
--(4)生成流水号
if object_id('tb') is not null drop table tb
drop function dbo.FC_Next
create function dbo.FC_Next()
returns char(8)
as
begin
return (select 'BH'+right(1000001+isnull(right(max(BH),6),0),6) from tb)
end
create table tb
(
BH char(8) primary key default dbo.FC_Next(),
col int
)
select * from tbbegin tran
insert into tb (col) values (1)
insert into tb (col) values (2)
insert into tb(BH,col) values (dbo.FC_Next(),14)
commit tran
select * from tb
--> (让你望见影子的墙)生成测试数据,时间:2009-03-14
if not object_id('tb') is null
drop table tb
Go
Create table tb([DWID] int,[name] nvarchar(3),[BH_1] nvarchar(1),[BH_2] nvarchar(3))
Insert tb
select 1,N'AAA',N'A',N'001' union all
select 2,N'BBB',N'B',N'002' union all
select 3,N'CCC',N'C',N'003' union all
select 4,N'DDD',N'D',N'004'
Go
Select * from tbif not object_id('tbb') is null
drop table tbb
Go
Create table tbb([ID] int,[DWID] int,[Number]varchar(10),[px] int,[name] nvarchar(4))
Insert tbb
select 1,1,null,0,N'abcd' union all
select 2,1,null,0,N'abcd' union all
select 3,2,null,0,N'abcd' union all
select 4,2,null,0,N'abcd' union all
select 5,3,null,0,N'abcd' union all
select 6,1,null,1,N'abcd' union all
select 7,1,null,1,N'abcd' union all
select 8,4,null,1,N'abcd' union all
select 9,3,null,1,N'abcd'
Go
select id,tb.dwid,number=case when px=0 then bh_1+convert(char(8),getdate(),112)+cast((select count(*) from tbb where id<=t.id and dwid=t.dwid and px=t.px) as varchar(10))+'F'
when px=1 then convert(char(8),getdate(),112)+cast((select count(*) from tbb where id<=t.id and dwid=t.dwid and px=t.px)as varchar(10))
end
,px,t.name
from tb join tbb t on tb.dwid=t.dwid
1 1 A200903141F 0 abcd
2 1 A200903142F 0 abcd
3 2 B200903141F 0 abcd
4 2 B200903142F 0 abcd
5 3 C200903141F 0 abcd
6 1 200903141 1 abcd
7 1 200903142 1 abcd
8 4 200903141 1 abcd
9 3 200903141 1 abcd
格式为A090001F 增值为"0001" -> A09"0001" (这里"09"为日期,要格式化一下)
格式为20090010002 增值为"0002" -> 2009001"0002"
RETURNS varchar(12)
AS
BEGIN
DECLARE @dt CHAR(4)
SELECT @dt=dt FROM v_GetDate
RETURN(
SELECT 'A'+@dt+RIGHT(10001+ISNULL(RIGHT(MAX(ID),4),0),4)
FROM TABLE WITH(XLOCK,PAGLOCK)
WHERE ID like 'A'+@dt+'%')
END
这是以前经论坛朋友帮助解决的一个自动编号的案例,得到的编号为"A"+日期+(递增的值) -> A20090001 A20090002
能不能在此基础上改一下
1 1 A090001F 0 abcd
2 1 A090002F 0 abcd
3 2 B090001F 0 abcd
4 2 B090002F 0 abcd
5 3 C090001F 0 abcd
6 1 20090010001 1 abcd
7 1 20090010002 1 abcd
8 4 20090040001 1 abcd
9 3 20090030001 1 abcd
create trigger trig_TABLE2_insert on table2
for insert
as
update a set number=case b.px
when '0' then c.BH_1+right(year(getdate()),2)+ case
when right((select number from table2 where id=(select max(id) from table2)),1)='F'
then right('0000'+(substring((select number from table2 where id=(select max(id) from table2)),4,4)+1),4)
else right('0000'+(right((select number from table2 where id=(select max(id) from table2)),4)+1),4) end+'F'
when '1'then ltrim(year(getdate()))+c.BH_2+case
when right((select number from table2 where id=(select max(id) from table2)),1)='F'
then right('0000'+(substring((select number from table2 where id=(select max(id) from table2)),4,4)+1),4)
else right('0000'+(right((select number from table2 where id=(select max(id) from table2)),4)+1),4) end
end
from TABLE2 a,inserted b,table1 c where a.id=b.id and a.DWID=c.DWID
create trigger trig_TABLE2_insert on table2
for insert
as
update a set number=case b.px
when '0' then c.BH_1+right(year(getdate()),2) when '1' then ltrim(year(getdate()))+c.BH_2 end+ case
when right((select number from table2 where id=(select max(id) from table2)),1)='F'
then right('0000'+(substring((select number from table2 where id=(select max(id) from table2)),4,4)+1),4)
else right('0000'+(right((select number from table2 where id=(select max(id) from table2)),4)+1),4) end+case b.px when '0' then 'F' else ''end
from TABLE2 a,inserted b,table1 c where a.id=b.id and a.DWID=c.DWID
--总算看明白了。
create trigger trig_TABLE2_insert on table2
for insert
as
update a set number=case b.px
when '0' then c.BH_1+right(year(getdate()),2) +right('0000'+((select max(substring(number,4,4)) from table2 where left(number,3)=c.BH_1+right(year(getdate()),2))+1),4)+'F'
when '1' then ltrim(year(getdate()))+c.BH_2+right('0000'+((select max(right(number,4)) from table2 where left(number,7)=ltrim(year(getdate()))+c.BH_2)+1),4) end
from TABLE2 a,inserted b,table1 c where a.id=b.id and a.DWID=c.DWID
create trigger trig_TABLE2_insert on table2
for insert
as
update a set number=case b.px
when '0' then c.BH_1+right(year(getdate()),2) +right('0000'+(isnull((select max(substring(number,4,4)) from table2 where left(number,3)=c.BH_1+right(year(getdate()),2)),'0000')+1),4)+'F'
when '1' then ltrim(year(getdate()))+c.BH_2+right('0000'+(isnull((select max(right(number,4)) from table2 where left(number,7)=ltrim(year(getdate()))+c.BH_2),'0000')+1),4) end
from TABLE2 a,inserted b,table1 c where a.id=b.id and a.DWID=c.DWID
declare @i int,@t bit,@string varchar(50),@s int
select @i=1
select @t=1
if @t=0
begin
if (select 1 from Table_2 where DWID=@i)=1
begin
select @s=max(convert(int,left(right(Number,5),4)))+1 from Table_2 where DWID=@i and px=@t
end
else
select @s=1
select @string=BH_1+convert(varchar(5),getdate())+right('0000'+convert(varchar(5),@s),4)+'F' from Table_1 where DWID=@i
end
if @t=1
begin
if (select 1 from Table_2 where DWID=@i)=1
begin
select @s=max(convert(int,right(Number,4)))+1 from Table_2 where DWID=@i and px=@t
end
else
select @s=1
select @string=convert(varchar(5),getdate())+ BH_2++right('0000'+convert(varchar(5),@s),4) from Table_1 where DWID=@i
end
insert Table_2
select @i,@string,@t,'abcd'
(
DWID int ,
name varchar(50) ,
BH_1 varchar(50) ,
BH_2 varchar(50)
)insert Table_1
select 1,'AAA','A','001' union all
select 2,'BBB','B','002' union all
select 3,'CCC','C','003' union all
select 4,'DDD','D','004'
create table Table_2
(
ID int identity(1,1),
DWID int ,
Number varchar(50) ,
px bit ,
name varchar(50),
)drop table Table_2
select * from Table_2declare @i int,@t bit,@string varchar(50),@s int
select @i=1
select @t=1
if @t=0
begin
if (select 1 from Table_2 where DWID=@i)=1
begin
select @s=max(convert(int,left(right(Number,5),4)))+1 from Table_2 where DWID=@i and px=@t
end
else
select @s=1
select @string=BH_1+convert(varchar(5),getdate())+right('0000'+convert(varchar(5),@s),4)+'F' from Table_1 where DWID=@i
end
if @t=1
begin
if (select 1 from Table_2 where DWID=@i)=1
begin
select @s=max(convert(int,right(Number,4)))+1 from Table_2 where DWID=@i and px=@t
end
else
select @s=1
select @string=convert(varchar(5),getdate())+ BH_2++right('0000'+convert(varchar(5),@s),4) from Table_1 where DWID=@i
end
insert Table_2
select @i,@string,@t,'abcd'
create table Table_1
(
DWID int ,
N_ame varchar(50) ,
BH_1 varchar(50) ,
BH_2 varchar(50)
) insert Table_1
select 1,'AAA','A','001' union all
select 2,'BBB','B','002' union all
select 3,'CCC','C','003' union all
select 4,'DDD','D','004'
create table Table_2
(
I_D int identity(1,1),
DWID int ,
Number varchar(50) ,
px bit ,
N_ame varchar(50),
)
create trigger trig_TABLE2_insert on Table_2
for insert
as
update a set number=case b.px
when '0' then c.BH_1+right(year(getdate()),2) +right('0000'+(isnull((select max(substring(number,4,4)) from table_2 where left(number,3)=c.BH_1+right(year(getdate()),2)),'0000')+1),4)+'F'
when '1' then ltrim(year(getdate()))+c.BH_2+right('0000'+(isnull((select max(right(number,4)) from table_2 where left(number,7)=ltrim(year(getdate()))+c.BH_2),'0000')+1),4) end
from TABLE_2 a,inserted b,Table_1 c where a.I_D=b.I_D and a.DWID=c.DWID
insert into Table_2 (Dwid,px) values ('4','1')select * from Table_2SELECT * FROM TABLE_1
有点不对初始的值为俄... 老大再帮忙看看吧.
for insert
as
update a set number=case b.px
when '0' then c.BH_1+right(year(getdate()),2) +right('0000'+ltrim((isnull((select max(substring(number,4,4)) from table_2 where left(number,3)=c.BH_1+right(year(getdate()),2)),'')+1)),4)+'F'
when '1' then ltrim(year(getdate()))+c.BH_2+right('0000'+ltrim((isnull((select max(right(number,4)) from table_2 where left(number,7)=ltrim(year(getdate()))+c.BH_2),'')+1)),4) end
from TABLE_2 a,inserted b,Table_1 c where a.I_D=b.I_D and a.DWID=c.DWID go
insert into Table_2 (Dwid,px) values ('1','1')
go
select * from Table_2
go
/*
I_D DWID Number px N_ame
----------- ----------- -------------------------------------------------- ---- --------------------------------------------------
1 1 A090001F 0 NULL
2 1 A090002F 0 NULL
3 1 20090010001 1 NULL
4 1 20090010002 1 NULL
5 1 20090010003 1 NULL(所影响的行数为 5 行)
*/
alter trigger trig_TABLE2_insert on Table_2
for insert
as
update a set number=case b.px
when '0' then c.BH_1+right(year(getdate()),2) +right('0000'+ltrim((isnull((select max(substring(number,4,4)) from table_2 where left(number,3)=c.BH_1+right(year(getdate()),2)),'')+1)),4)+'F'
when '1' then ltrim(year(getdate()))+c.BH_2+right('0000'+ltrim((isnull((select max(right(number,4)) from table_2 where left(number,7)=ltrim(year(getdate()))+c.BH_2),'')+1)),4) end
from TABLE_2 a,inserted b,Table_1 c where a.I_D=b.I_D and a.DWID=c.DWID go
insert into Table_2 (Dwid,px) values ('1','1')
go
select * from Table_2
go
/*
I_D DWID Number px N_ame
----------- ----------- -------------------------------------------------- ---- --------------------------------------------------
1 1 A090001F 0 NULL
2 1 A090002F 0 NULL
3 1 20090010001 1 NULL
4 1 20090010002 1 NULL
5 1 20090010003 1 NULL(所影响的行数为 5 行)
*/