我想插入数据库一个字段是这样的:
ID PID
1 0911200001 当天时间091120+0001
2 0911200002 当天时间091120+0002
依次类推
0911210001 第二天又是取当前时间+0001
0911210002 第二天又是取当前时间+0002
依次类推
0911220001 第二天又是取当前时间+0001
.......
这个要怎么实现哦!!!帮帮忙(ID不用管,只求那个编号)
ID PID
1 0911200001 当天时间091120+0001
2 0911200002 当天时间091120+0002
依次类推
0911210001 第二天又是取当前时间+0001
0911210002 第二天又是取当前时间+0002
依次类推
0911220001 第二天又是取当前时间+0001
.......
这个要怎么实现哦!!!帮帮忙(ID不用管,只求那个编号)
Id序号我设了自动加一,FormatId我想他也象这样"SL000001",
当Insert时就加1,FormatId我想他也能自动加一"SL000001","SL000002"...
能用一条sql什么办法实现.最好不要用中间表。有什么好方法?
谢谢!
create table #test
(id int identity,
FormatId as 'SL'+right(10000000+id,6),
F1 varchar(50))
go
insert #test(F1) select '1'
union all select '2'
select * from #testdrop table #test
/*
id FormatId F1
----------- -------------- -----
1 SL000001 1
2 SL000002 2(所影响的行数为 2 行)
*/
在学习中遇到这个问题
数据库里有编号字段
BH00001
BH00002
BH00003
BH00004
如何实现自动增长 --下面的代码生成长度为8的编号,编号以BH开头,其余6位为流水号。
--得到新编号的函数
CREATE FUNCTION f_NextBH()
RETURNS char(8)
AS
BEGIN
RETURN(SELECT 'BH'+RIGHT(1000001+ISNULL(RIGHT(MAX(BH),6),0),6) FROM tb WITH(XLOCK,PAGLOCK))
END
GO--在表中应用函数
CREATE TABLE tb(
BH char(8) PRIMARY KEY DEFAULT dbo.f_NextBH(),
col int)--插入资料
BEGIN TRAN
INSERT tb(col) VALUES(1)
INSERT tb(col) VALUES(2)
INSERT tb(col) VALUES(3)
DELETE tb WHERE col=3
INSERT tb(col) VALUES(4)
INSERT tb(BH,col) VALUES(dbo.f_NextBH(),14)
COMMIT TRAN--显示结果
SELECT * FROM tb
/*--结果
BH col
---------------- -----------
BH000001 1
BH000002 2
BH000003 4
BH000004 14
--*/ create table tb
(id int identity,
name varchar(10),
code as 'BH'+right('0000'+cast(id as varchar),5))
go
insert tb(name) select 'A'
union all select 'B'
union all select 'C'
union all select 'D'select * from tbdrop table tb/*
id name code
----------- ---------- ------------
1 A BH00001
2 B BH00002
3 C BH00003
4 D BH00004(所影响的行数为 4 行)
*/
在程序中生成:
datetime.now()
在sql中
select substring(convert(nchar(10),getdate(),112),3,6)+'00001'这个只根据当天的最大值
gocreate trigger my_trig on tb FOR INSERT
as
begin
declare @cnt as int
select @cnt = count(1) from tb where pid = (select pid from inserted)
update tb set pid2 = pid + right('0000'+cast(@cnt as varchar),4) where id = (select id from inserted)
end
goinsert into tb(pid) values('091120')
insert into tb(pid) values('091120')
insert into tb(pid) values('091121')
insert into tb(pid) values('091121')select * from tbdrop table tb/*ID PID PID2
----------- -------------------- --------------------
1 091120 0911200001
2 091120 0911200002
3 091121 0911210001
4 091121 0911210002(所影响的行数为 4 行)
*/
测试过没问题
declare @tb1 table([ID] int,[PID] varchar(10))
insert @tb1
select 1,'0911200001'
select (case when t2.id is null then 1 else t2.id+1 end) as ID,
(case
when t2.[PID] is null then convert(nvarchar(20),getdate(),11)+'0001'
else convert(nvarchar(20),getdate(),12)+right('000'+cast(t2.ID+1 as nvarchar(5)),4)
end)
from @tb1 t1 left join (
select [ID], left([PID],6) as [PID] from @tb1 where datediff(dd,left([PID],6),getdate())=0
) as t2 on left(t1.[PID],6) = t2.PID
if object_id('TB') is not null drop table TB
GO
create table TB (ID int IDENTITY(1,1),PID varchar(20))
insert into TB
select '0911200001' union all
select '0911200002'
if object_id('PP') is not null drop PROC PP
GO
CREATE PROC PP
@TIME DATETIME
AS
IF EXISTS(SELECT 1 FROM TB WHERE LEFT(PID,6)=RIGHT(CONVERT(VARCHAR(10),@TIME,112),6))
INSERT TB SELECT RIGHT(CONVERT(VARCHAR(10),@TIME,112),6)+RIGHT('0000'+LTRIM(MAX(RIGHT(PID,4))+1),4) FROM TB
ELSE
INSERT TB SELECT RIGHT(CONVERT(VARCHAR(10),@TIME,112),6)+'0001'
GO
EXEC PP '2009-11-20'
SELECT * FROM TB
/*
ID PID
----------- --------------------
1 0911200001
2 0911200002
3 0911200003(3 行受影响)
*/EXEC PP '2009-11-21'
SELECT * FROM TB ID PID
----------- --------------------
1 0911200001
2 0911200002
3 0911200003
4 0911210001(4 行受影响)
CREATE PROC PP
AS
IF EXISTS(SELECT 1 FROM TB WHERE LEFT(PID,6)=RIGHT(CONVERT(VARCHAR(10),getdate(),112),6))
INSERT TB SELECT RIGHT(CONVERT(VARCHAR(10),getdate(),112),6)+RIGHT('0000'+LTRIM(MAX(RIGHT(PID,4))+1),4) FROM TB
ELSE
INSERT TB SELECT RIGHT(CONVERT(VARCHAR(10),getdate(),112),6)+'0001'
GO
exec pp
select * from tb