如何每天重置seqence订单？ [英] how to reset the seqence order day to day?

问题描述

朋友在这里我需要通过使用日期来生成日常序列....例如：今天我的第一个id序列是2015/06/25/001，明天我的第一个id将是2015 / 06/26 / 001..请用例子解释

谢谢.....

friends here i need to generate day to day sequence order by using date.... for example: today my first id sequence is 2015/06/25/001 and tomorrow my first id will be 2015/06/26/001..please explain with examples
thank you.....

推荐答案

在SQL Server中你可以像

要获得第一个数字，

In SQL Server you can do it like
To get the first number,

SELECT CAST(DATEPART(YEAR,GETDATE()) AS VARCHAR)+'/'+RIGHT('0'+CAST(DATEPART(MONTH,GETDATE()) AS VARCHAR),2)+'/'+RIGHT('0'+CAST(DATEPART(DAY,GETDATE()) AS VARCHAR),2)+'/'+'001'

您可以动态查看某些字段获取最新值的表格如

You can make it dynamic to check with some field of a table to get latest value like

DECLARE @OrderSequenceBase VARCHAR(20)
SELECT @OrderSequenceBase= CAST(DATEPART(YEAR,GETDATE()) AS VARCHAR)+'/'+RIGHT('0'+CAST(DATEPART(MONTH,GETDATE()) AS VARCHAR),2)+'/'+RIGHT('0'+CAST(DATEPART(DAY,GETDATE()) AS VARCHAR),2)+'/'

DECLARE @OrderSequenceTrail VARCHAR(3)
SELECT @OrderSequenceTrail=CASE WHEN OrderSequence LIKE @OrderSequenceBase+'%' THEN ISNULL (RIGHT(OrderSequence,3),'0') ELSE '0' END
FROM YourTable
WHERE OrderSequence LIKE @OrderSequenceBase+'%'

SELECT @OrderSequenceBase+RIGHT('00'+CAST(CAST(@OrderSequenceTrail AS INT)+1 AS VARCHAR),3)

示例：

Example:

DECLARE @OrderSequenceBase VARCHAR(20)
SELECT @OrderSequenceBase= CAST(DATEPART(YEAR,GETDATE()) AS VARCHAR)+'/'+RIGHT('0'+CAST(DATEPART(MONTH,GETDATE()) AS VARCHAR),2)+'/'+RIGHT('0'+CAST(DATEPART(DAY,GETDATE()) AS VARCHAR),2)+'/'
 
DECLARE @OrderSequence VARCHAR(20)
SELECT @OrderSequence='2015/06/25/003'
DECLARE @OrderSequenceTrail VARCHAR(3)
SELECT @OrderSequenceTrail=CASE WHEN @OrderSequence LIKE @OrderSequenceBase+'%' THEN ISNULL (RIGHT(@OrderSequence,3),'0') ELSE '0' END
 

SELECT @OrderSequenceBase+RIGHT('00'+CAST(CAST(@OrderSequenceTrail AS INT)+1 AS VARCHAR),3)

我不知道PostgreSQL，但这种方法应该可行。



希望，它有帮助：）

I have no idea about PostgreSQL but the approach should work.

Hope, it helps :)

这篇关于如何每天重置seqence订单？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！