要做这样一个事情,向数据库中插入记录,记录有一个ID字段,
id形式如:
ID
d-1
d-2
s-1
s-2
c-1
c-2
现在插入一条新记录,是s公司的,插入进去后它的ID应该为s3,如何判断这个“s3”中的3呢???现在的办法只想到了先查询数据库,得到s*中最大的,那么这个新插入的记录就是s(*+1)....有没有其他好方法呢????
id形式如:
ID
d-1
d-2
s-1
s-2
c-1
c-2
现在插入一条新记录,是s公司的,插入进去后它的ID应该为s3,如何判断这个“s3”中的3呢???现在的办法只想到了先查询数据库,得到s*中最大的,那么这个新插入的记录就是s(*+1)....有没有其他好方法呢????
select 'd-1' union all
select 'd-2' union all
select 's-1' union all
select 's-2' union all
select 'c-1' union all
select 'c-2'
--select * from #tdeclare @a varchar(10),@maxID varchar(100)set @a='s'--取当前公司最大的加1
set @maxID=(select max(cast(substring(id,charindex('-',id)+1,len(id)-charindex('-',id)) as int))
from #t
where id like @a + '-%')select @a + cast(isnull(@maxID,0)+1 as varchar(100))
drop table #t
/*
s3*/
insert into #t (id1,id2) select 's',2
insert into #t (id1,id2) select 's',3--这样追加时,只需要过滤ID1='s',取id2最大的+1即可,系统自动生成IDselect * from #tdrop table #t
drop table tb
gocreate table tb(ID varchar(10))
insert into tb(ID) values('d-1')
insert into tb(ID) values('d-2')
insert into tb(ID) values('s-1')
insert into tb(ID) values('s-2')
insert into tb(ID) values('c-1')
insert into tb(ID) values('c-2')
godeclare @str as varchar(10)
declare @maxstr as varchar(10)
set @str = 's'select @maxstr = max(id) from tb where id like '%' + @str + '%'
if @maxstr is null
insert into tb(id) values(@str + '-1')
else
insert into tb(id) values(@str + '-' + cast(cast(substring(@maxstr,charindex('-',@maxstr) + 1 ,len(@maxstr)) as int) + 1 as varchar))select * from tbdrop table tb/*
ID
----------
d-1
d-2
s-1
s-2
c-1
c-2
s-3(所影响的行数为 7 行)*/
类似 max(*) where field1=s ,不知道这个方法和存成一个字段用存储过程哪个效率高啊???
insert into ta
select 'd-1' union all
select 'd-2' union all
select 's-1' union all
select 's-2' union all
select 'c-1' union all
select 'c-2'insert ta(id) select create proc test
@id varchar(100)
as
select @id=max(@id+'-'+rtrim(stuff(id,1,2,'')+1)) from ta where left(id,1)=@id
insert ta values(@id)
--测试:
exec test 's'--公司select * from ta
id
----------------------------------------------------------------------------------------------------
d-1
d-2
s-1
s-2
c-1
c-2
s-3(7 行受影响)
别的方法也就是触发器了,不过也一样是计算建议在SQL里算吧
@input varchar(10)
as
declare @i int if not exists(select 1 from t1 where charindex(@input,id)>0 and left( id,charindex('-',id)-1)=@input)
begin
insert into t1(id) select @input+'-'+'1'
end
else
begin
select @i=max(right(id,len(id)-charindex('-',id))) from t1
where charindex(@input,id)>0 and left( id,charindex('-',id)-1)=@input
insert into t1 select @input+'-'+rtrim(@i+1)
end
GO
CREATE TABLE t1(ID VARCHAR(20))GO
insert into t1
select 'd-1' union all
select 'd-2' union all
select 's-1' union all
select 's-2' union all
select 'c-1' union all
select 'c-2'
GO
DROP PROC inputstr
GO
CREATE PROCEDURE inputstr
@input VARCHAR(10)
AS
DECLARE @i INT IF NOT EXISTS(SELECT 1 FROM t1 WHERE CHARINDEX (@input,ID)>0 AND LEFT( ID,CHARINDEX('-',ID)-1)=@input)
BEGIN
INSERT INTO t1(ID) SELECT @input+'-'+'1'
END
ELSE
BEGIN
SELECT @i=MAX(CAST(RIGHT(ID,LEN(ID)-CHARINDEX('-',ID)) AS INT))
FROM t1
WHERE CHARINDEX(@input,ID)>0
AND LEFT( ID,CHARINDEX('-',ID)-1) = @input
PRINT @I
INSERT INTO t1 SELECT @input+'-'+RTRIM(@i+1)
END
GO
EXEC inputstr 'S'SELECT * FROM T1 WHERE ID LIKE 'S%'
delimiter //
create procedure test(in sprefix varchar(11))
begindeclare maxStr varchar(11);
select maxStr = max(keyid) from test where keyid like sprefix + '%';
if maxStr is null
insert into test (keyid) values (sprefix + '-1');
else
insert into test (keyid) values (sprefix +'-'+cast(cast(substring(maxStr,locate('-',maxStr) + 1),length(maxStr)) as int) + 1 +as varchar));end
//delimiter ;
select 's'+MAX(RIGHT(id,CHARINDEX('S-',id,0)))+1
from mytable
INSERT INTO mytable
SELECT 'S-1'
UNION ALL
SELECT 'S-2'
UNION ALL
SELECT 'S-3'
UNION ALL
SELECT 'D-1'
UNION ALL
SELECT 'C-1'SELECT * FROM mytableinsert into mytable (ID)
select 'S-'+CAST(MAX(RIGHT(id,CHARINDEX('S-',id,0)))+1 AS VARCHAR(50))
from mytable
SELECT * FROM mytable
DROP TABLE mytable