MS SQL中如何生成基于当前日期+（max_number+1）的序列号？

Got it, let's tackle this serial number generation problem for your MS SQL table. From what you described, we need a serial number that combines the current date in YYYYMMDD format with 1 plus the largest numeric suffix from existing same-date serial numbers. If there are no entries for the day, we start at 1 (like 201812231 for the first entry on December 23rd, 2018).

Core Approach #

• Grab the current date as an 8-digit string (e.g., 20181222).
• Query your table to find all serial numbers starting with this date string, extract their numeric suffixes, and get the maximum value.
• Handle the edge case where there are no same-date entries (treat the max suffix as 0, so adding 1 gives us 1).
• Concatenate the date string with the new sequential number to get your final serial number.

Example SQL Code #

Let's assume your table is named YourTable and the serial number column is SerialNumber (stored as a string). Here's a query that generates the next valid serial number:

DECLARE @CurrentDateStr VARCHAR(8) = FORMAT(GETDATE(), 'yyyyMMdd');
DECLARE @MaxSuffix INT;

-- Fetch the largest numeric suffix for today's serial numbers; default to 0 if none exist
SELECT @MaxSuffix = ISNULL(MAX(CAST(SUBSTRING(SerialNumber, 9, LEN(SerialNumber) - 8) AS INT)), 0)
FROM YourTable
WHERE SerialNumber LIKE @CurrentDateStr + '%'
WITH (UPDLOCK, HOLDLOCK); -- Add locks to prevent duplicate serial numbers in concurrent scenarios

-- Build the new serial number
DECLARE @NewSerialNumber VARCHAR(20) = @CurrentDateStr + CAST(@MaxSuffix + 1 AS VARCHAR);

SELECT @NewSerialNumber AS NewSerialNumber;

Breakdown of the Code #

• @CurrentDateStr: Uses FORMAT(GETDATE(), 'yyyyMMdd') to convert the current date into an 8-digit string (e.g., 20181222).
• Extracting the Suffix: SUBSTRING(SerialNumber, 9, LEN(SerialNumber)-8) grabs everything after the first 8 characters (the date part), then we cast that substring to an integer to work with numeric values.
• Handling No Entries: ISNULL(MAX(...), 0) ensures if there are no serial numbers for the day, we start counting from 1 instead of getting a NULL error.
• Concurrency Protection: WITH (UPDLOCK, HOLDLOCK) adds locks during the query to prevent multiple sessions from reading the same max suffix at the same time, which would lead to duplicate serial numbers.

If Your Serial Numbers Are Stored as Integers #

If SerialNumber is an integer (instead of a string), adjust the logic to separate the date and numeric parts mathematically:

DECLARE @CurrentDateInt INT = CAST(FORMAT(GETDATE(), 'yyyyMMdd') AS INT);
DECLARE @MaxSuffix INT;

-- Calculate the max suffix by subtracting the date portion from the serial number
SELECT @MaxSuffix = ISNULL(MAX(SerialNumber - @CurrentDateInt * 10000), 0)
FROM YourTable
WHERE SerialNumber >= @CurrentDateInt * 10000 
  AND SerialNumber < (@CurrentDateInt + 1) * 10000
WITH (UPDLOCK, HOLDLOCK);

-- Generate the new integer serial number
DECLARE @NewSerialNumber INT = @CurrentDateInt * 10000 + (@MaxSuffix + 1);

SELECT @NewSerialNumber AS NewSerialNumber;

Note: The 10000 multiplier assumes your numeric suffix won't exceed 4 digits. If you need longer suffixes, use a larger multiplier (e.g., 100000 for 5-digit suffixes).

内容的提问来源于stack exchange，提问作者Mohammed Abdelwahab