wangdehao(找找找) 是的
lsqkeke() 一天不会超过999条
id ckrq kf ckbh
----------- ------------------------------------------------------ ---- ----------
178864 2005-11-28 00:00:00.000 8 051128001
178865 2005-11-28 00:00:00.000 8 051128002
178866 2005-11-28 00:00:00.000 8 051128003
178867 2005-11-28 00:00:00.000 8 051128004
178868 2005-11-28 00:00:00.000 8 051128005
178869 2005-11-28 00:00:00.000 8 051128006
178870 2005-11-28 00:00:00.000 8 051128007
178871 2005-11-28 00:00:00.000 8 051128008
178872 2005-11-28 00:00:00.000 8 051128009
178873 2005-11-28 00:00:00.000 8 051128010(所影响的行数为 10 行)
lsqkeke() 一天不会超过999条
id ckrq kf ckbh
----------- ------------------------------------------------------ ---- ----------
178864 2005-11-28 00:00:00.000 8 051128001
178865 2005-11-28 00:00:00.000 8 051128002
178866 2005-11-28 00:00:00.000 8 051128003
178867 2005-11-28 00:00:00.000 8 051128004
178868 2005-11-28 00:00:00.000 8 051128005
178869 2005-11-28 00:00:00.000 8 051128006
178870 2005-11-28 00:00:00.000 8 051128007
178871 2005-11-28 00:00:00.000 8 051128008
178872 2005-11-28 00:00:00.000 8 051128009
178873 2005-11-28 00:00:00.000 8 051128010(所影响的行数为 10 行)
on test
for insert
as
declare @year char(6),@kf char,@num int
--select @id=max(id) from test
select @year=convert(char(8),ckrq,12),@kf=kf from test where id=(select id from inserted)
select @num=isnull(max(cast(right(ckbh,3) as int)),0)+1 from test where convert(char(8),ckrq,12)=@year and kf=@kf
update test set ckbh=@year+@kf +replicate('0',3-datalength(cast(@num as varchar)))+cast(@num as varchar)
where id=(select id from inserted)
--測試︰
insert into test(ckrq,kf) values(getdate(),1)
insert into test(ckrq,kf) values(getdate(),1)
insert into test(ckrq,kf) values(getdate(),1)
insert into test(ckrq,kf) values(getdate(),2)
insert into test(ckrq,kf) values(getdate(),2)
select * from test
結果︰
id ckrq kf ckbh
----------- ------------------------------------------------------ ---- ----------
1 2005-12-13 15:24:47.537 1 0512131001
2 2005-12-13 15:25:02.020 1 0512131002
3 2005-12-13 15:25:02.020 1 0512131003
4 2005-12-13 15:25:25.317 2 0512132001
5 2005-12-13 15:25:25.317 2 0512132002
您这是用于生成时的吧,可是如果我想对原来数据表中的内容做修改得怎么办呢?也就是说我原来的数据表中存在数据.怎么样才能把原来的错误数据改过来呢?主要是原来的ckbh有重复的,我想用这种方法把ckbh改成没有重复的数据,然后再变成主键!
的方法,declare @year char(6),@kf char,@num int
--select @id=max(id) from test
select @year=convert(char(8),ckrq,12),@kf=kf from part_out where id=(select id from part_out)
select @num=isnull(max(cast(right(ckbh,3) as int)),0)+1 from part_out where convert(char(8),ckrq,12)=@year and kf=@kf
update part_out set ckbh=@year+@kf +replicate('0',3-datalength(cast(@num as varchar)))+cast(@num as varchar)
where id=(select id from part_out)又出现以下错误
服务器: 消息 512,级别 16,状态 1,行 3
子查询返回的值多于一个。当子查询跟随在 =、!=、<、<=、>、>= 之后,或子查询用作表达式时,这种情况是不允许的。
服务器: 消息 512,级别 16,状态 1,行 5
子查询返回的值多于一个。当子查询跟随在 =、!=、<、<=、>、>= 之后,或子查询用作表达式时,这种情况是不允许的。
语句已终止。