我需要根據分銷商代碼生成訂單號，如下所示。訂單號格式如下
訂單號：分銷商代碼/零售商代碼/TO/DDMMYY訂單號（訂單號將為 0001,0002，...）
Scenario 1 Order Uploaded Console time: 11AM (08-Jan'22)
Retailer 1 22520/1400002/TO/0801220001
Retailer 2 22520/1400003/TO/0801220002
Retailer 3 22520/1400004/TO/0801220003
Retailer 4 22520/1400005/TO/0801220004
Retailer 5 22520/1400006/TO/0801220005
Retailer 6 22520/1400007/TO/0801220006
Same Distributor different retailers. Order number carries forward Distributor wise and not retailer wise.
和
Scenario 2 Order Uploaded Console time 2PM (08-Jan'22)
Retailer 1 22520/1400002/TO/0801220007
Retailer 2 22520/1400003/TO/0801220008
Retailer 7 22520/1400011/TO/0801220009
Retailer 8 22520/1400012/TO/0801220010
Same Distributor different retailers and same retailers. For same retailers for the "same product" same or unique quantity gets uploaded.
和
Scenario 3 Order Uploaded Console time 10AM (14-Jan'22)
Retailer 4 22520/1400005/TO/1401220011
Retailer 5 22520/1400006/TO/1401220012
Retailer 6 22520/1400007/TO/1401220013
Retailer 8 22520/1400012/TO/1401220014
Same Distributor different day order was uploaded. Please note the series
我已經使用以下 SP 將一些資料從 EXCEL 上傳到 DATABASE
CREATE PROC [dbo].[Proc_TeleOrderUpload]
(
@ORDER_UPLOAD NTEXT = NULL,
@UserId INT = 0
)
AS
BEGIN
DECLARE @idoc AS INT
Create Table #Tbl_TeleOrderUpload
(
DistCode NVARCHAR(50),
CompRtrCode NVARCHAR(50),
ProductCode NVARCHAR(50),
QtyInPieces Varchar(10),
errStatus INT
)
EXEC sp_xml_preparedocument @idoc OUTPUT, @ORDER_UPLOAD
INSERT INTO #Tbl_TeleOrderUpload
SELECT
DistCode,
CompRtrCode,
ProductCode,
QtyInPieces,
0 AS errStatus
FROM OPENXML (@idoc, '/TeleOrderUpload/Record',2)
WITH
(
DistCode NVARCHAR(50),
CompRtrCode NVARCHAR(50),
ProductCode NVARCHAR(50),
QtyInPieces Varchar(10)
)
EXEC sp_xml_removedocument @idoc
INSERT INTO Tbl_TeleOrderUpload
(
DistCode,
CompRtrCode,
ProductCode,
QtyInPieces,
OrderTaken,
OrderNumber,
UploadFlag,
CreatedId,
CreatedDate
)
SELECT DISTINCT
DistCode,
CompRtrCode,
ProductCode,
CAST(QtyInPieces AS int),
'Console',
'',
'Y',
@UserId,
GETDATE()
FROM
#Tbl_TeleOrderUpload
SELECT 'DATA UPLOADED SUCCESSFULLY...'
END
在此，我需要插入上面的訂單號。這里游標不應該用來更新訂單號。我需要實作以下邏輯
DECLARE @number AS INT = 0
SET @number = (SELECT MAX(RIGHT(OrderNumber,6)) FROM Tbl_TeleOrderUpload WHERE DistCode=@DistCode)
IF @number IS NULL
SET @number=1
ELSE
SET @number =@number 1
SELECT RIGHT('000000' CAST(@number AS VARCHAR(6)) , 6)
樣本資料如下：
CREATE TABLE [dbo].[Tbl_TeleOrderUpload](
[DistCode] [nvarchar](50) NULL,
[CompRtrCode] [nvarchar](50) NULL,
[ProductCode] [nvarchar](50) NULL,
[QtyInPieces] [int] NULL,
[OrderTaken] [varchar](10) NULL,
[OrderNumber] [varchar](100) NULL,
[UploadFlag] [varchar](10) NULL,
[CreatedId] [int] NULL,
[CreatedDate] [datetime] NULL
)
INSERT INTO Tbl_TeleOrderUpload VALUES ('0000014811','148111600220','GNOTNA06',1,'Console','','Y',1,GETDATE())
INSERT INTO Tbl_TeleOrderUpload VALUES ('0000014811','148111600220','GNOTNA06',2,'Console','','Y',1,GETDATE())
INSERT INTO Tbl_TeleOrderUpload VALUES ('0000014811','148111600220','GNOTNA06',3,'Console','','Y',1,GETDATE())
INSERT INTO Tbl_TeleOrderUpload VALUES ('0000014811','148111600220','GNOTNA06',4,'Console','','Y',1,GETDATE())
INSERT INTO Tbl_TeleOrderUpload VALUES ('0000014811','148111600220','GNOTNA06',5,'Console','','Y',1,GETDATE())
INSERT INTO Tbl_TeleOrderUpload VALUES ('0000015000','15001600220','GNOTNA06',1,'Console','','Y',1,GETDATE())
INSERT INTO Tbl_TeleOrderUpload VALUES ('0000015000','150011600220','GNOTNA06',2,'Console','','Y',1,GETDATE())
INSERT INTO Tbl_TeleOrderUpload VALUES ('0000015000','15001600220','GNOTNA06',3,'Console','','Y',1,GETDATE())
INSERT INTO Tbl_TeleOrderUpload VALUES ('0000015000','15001600220','GNOTNA06',4,'Console','','Y',1,GETDATE())
INSERT INTO Tbl_TeleOrderUpload VALUES ('0000015000','150011600220','GNOTNA06',5,'Console','','Y',1,GETDATE())
請指導我實作這一目標。
我的 SQL SERVER 版本是
Microsoft SQL Server 2008 R2 (RTM) - Windows NT 6.1 上的 10.50.1617.0 (X64) 標準版（64 位）（內部版本 7601：Service Pack 1）（管理程式）
提前致謝
uj5u.com熱心網友回復：
看起來像ROW_NUMBER()在 SQL 2008 中作業，所以可能這就是你想要的：
SELECT *, RIGHT('000000' CAST(@MyOrderNumber AS VARCHAR(6)) , 6)
FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY DistCode ORDER BY CompRtrCode) As MyOrderNumber
FROM Table
) ST
我敢肯定，您的要求遠不止這些。
這將從統計資料集中生成一些訂單號。如果您愿意，可以使用它來更新表格。
但它不考慮新記錄的出現。當新記錄出現在現有記錄的中間時，它絕對沒有考慮到
這就是為什么像這樣使用特殊的人工生成的 id 是個壞主意。
如果您希望能夠基于現有記錄并從那里增加訂單號，則解決方案會更加復雜。
SELECT ST.*, RIGHT('000000' CAST(ST.MyOrderNumber ISNULL(CN.HighestOrderNo) AS VARCHAR(6)) , 6)
FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY DistCode ORDER BY CompRtrCode) As MyOrderNumber
FROM Table
) ST
LEFT JOIN
(
SELECT DistCode, MAX(ExistingOrderNo) HighestOrderNo
GROUP BY DistCode
FROM Table
) CN
ON CN.DistCode = ST.DistCode
但我必須重申：
根據這樣的特殊規則生成密鑰是一個壞主意，因為隨著不同記錄的到達，最終會出現間隙和不一致的數字。
特別是這種情況：
Retailer 4 22520/1400005/TO/1401220011
Retailer 5 22520/1400006/TO/1401220012
Retailer 6 22520/1400007/TO/1401220013
Retailer 8 22520/1400012/TO/1401220014
現在您匯入同一天到達的下一批資料，它包含以下記錄：
Retailer 6 22520/1400008/TO/140122
它應該有什么訂單號？1400008 在 140007 之后，所以應該是 000014。但這已經被占用了。
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/407916.html
標籤：
下一篇：如何將專案從vb轉換為c#？