如何在SQL中获得一个唯一的数字,如上所述? [英] How to get a unique number in SQL as in the mentioned format?
本文介绍了如何在SQL中获得一个唯一的数字,如上所述?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我想在sql中获得一个唯一的数字。
格式如下:
DDMMYYXXXX
其中DDMMYY(当前日期在DDMMYY格式)和
XXXX一个4位数的经常性数字
我的尝试:
select @ test = REPLACE(CONVERT(CHAR(10),GETDATE(),103),'/','')
解决方案
检查: sql server - 如何自动增加varchar - Stack Overflow [ ^ ]
表定义应该如下(用你的名字替换YourTableName
想要使用):
CREATE TABLE YourTableName
(
ID INT IDENTITY ( 1 , 1 ) ,
CurrDate DATE ,
CID INT ,
VCID VARCHAR ( 10 )
)
让我们插入样本数据:
- 插入样本数据
INSERT INTO YourTableName(CurrDate, CID,VCID)
VALUES (' 2016 -08-23', 5 ,' 2308160005'),
(' 2016-08-23', 6 ,' 2308160006'),
(' 2016-08-23', 7 ,' 2308160007')CREATE 已存储程序 cspInsertMyData
< span class =code-keyword> AS
BEGIN
- 声明变量
DECLARE @ d < span class =code-keyword> DATE = GETDATE()
- max of CID
DECLARE @ nextcid INT = 0
- 启动变量
SELECT @ nextcid = COALESCE (MAX(CID), 0 )+ 1 FROM YourTableName
INSERT INTO YourTableName(CurrDate,CID,VCID)
VALUES ( @ d , @ nextcid ,FORMAT( @ d , ' ddMMyy')+ RIGHT (< span class =code-string>' 0000' + CONVERT ( VARCHAR ( 4 ), @ nextcid ), 4 ))
END ;
结果:
ID CurrDate CID VCID
1 2016-08-23 5 2308160005
2 2016-08-23 6 2308160006
3 2016-08-23 7 2308160007
4 2016-08-23 8 < span class =code-digit> 2308160008
[/ EDIT]
I want to get a unique number in sql.
Format is as below:
DDMMYYXXXX
where DDMMYY (Current Date in DDMMYY Format) and
XXXX a 4 digit recurring number
What I have tried:
select @test=REPLACE(CONVERT(CHAR(10), GETDATE() , 103), '/', '')
解决方案
Check this: sql server - How to autoincrement a varchar - Stack Overflow[^]
[EDIT]
A table definition should look like (replaceYourTableName
with the name you want to use):
CREATE TABLE YourTableName ( ID INT IDENTITY(1,1), CurrDate DATE, CID INT, VCID VARCHAR(10) )
Let's insert sample data:
--insert sample data INSERT INTO YourTableName(CurrDate, CID, VCID) VALUES('2016-08-23', 5, '2308160005'), ('2016-08-23', 6, '2308160006'), ('2016-08-23', 7, '2308160007')
Then you have to create stored procedure[^]
CREATE STORED PROCEDURE cspInsertMyData AS BEGIN --declare variables DECLARE @d DATE = GETDATE() --max of CID DECLARE @nextcid INT = 0 --initiate variable SELECT @nextcid = COALESCE(MAX(CID), 0) + 1 FROM YourTableName INSERT INTO YourTableName (CurrDate, CID, VCID) VALUES(@d, @nextcid, FORMAT(@d, 'ddMMyy') + RIGHT('0000' + CONVERT(VARCHAR(4), @nextcid), 4)) END;
A result:
ID CurrDate CID VCID 1 2016-08-23 5 2308160005 2 2016-08-23 6 2308160006 3 2016-08-23 7 2308160007 4 2016-08-23 8 2308160008
[/EDIT]
这篇关于如何在SQL中获得一个唯一的数字,如上所述?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文