22,209
社区成员
发帖
与我相关
我的任务
分享
IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'tba')
BEGIN
DROP TABLE tba
END
GO
CREATE TABLE tba
(
ID INT IDENTITY(1,1),
Fileid VARCHAR(100)
)
GO
IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'createid')
BEGIN
DROP FUNCTION CreateID
END
GO
CREATE FUNCTION CREATEID(@Type VARCHAR(10))
RETURNS VARCHAR(100)
AS
BEGIN
DECLARE @FileID VARCHAR(100)
DECLARE @Num INT
SELECT @Num = ISNULL(COUNT(1),0) FROM tba WHERE CHARINDEX(@Type,FileID) > 0 AND CHARINDEX(CONVERT(VARCHAR(8),GETDATE(),112),Fileid) > 0
SET @Num = @Num + 1
SET @FileID = @Type + CONVERT(VARCHAR(8),GETDATE(),112) + REPLICATE('0',3 - LEN(RTRIM(@Num))) + RTRIM(@Num)
RETURN @FileID
END
GO
INSERT INTO tba
SELECT dbo.CREATEID('T') UNION ALL
SELECT dbo.CREATEID('A') UNION ALL
SELECT dbo.CREATEID('V') UNION ALL
SELECT dbo.CREATEID('T') UNION ALL
SELECT dbo.CREATEID('T')
SELECT * FROM tba
ID Fileid
1 T20120619001
2 A20120619001
3 V20120619001
4 T20120619002
5 T20120619003