问题描述
亲爱的所有
请告诉我。
如何制作自动增量产品明智的商品代码数字
我必须将记录保存到数据库,因为我有10个产品( Milk
, Dairy
, Fresh Juice
)。现在,在我输入产品详细信息的初始过程中,假设我必须存储 MILK的值
我输入了所需的详细信息,现在我想在数据库中找到一个 ITEM CODE
下次输入<$ c $的详细信息时,应将值存储为增量计数器( MIL 100
) c> MILK ITEM CODE
应自动存储为 MIL002
。乳制品,鲜榨果汁等的情况也是如此......
例如
Dear ALL
Please tell me.
How to make Auto Increment Product wise Item code Number
I have to store record to database as I have 10 products (Milk
, Dairy
, Fresh Juice
). Now during the initial process when I enter the product details let say i have to store value for a MILK
I put in the required details, now I want that in the database a ITEM CODE
value should be stored as a increment counter (MIL 100
) next time when I enter details for MILK
the ITEM CODE
should be automatically stored as MIL002
. Same is the case with Dairy, Fresh Juice, etc...
For Example
Item Code
MIL100
DAI100
FRE100
FRE101
MIL101
FRE102
DAI101
推荐答案
DECLARE @items TABLE (ItemID INT IDENTITY(1,1), CategoryName VARCHAR(30), CategoryID INT, CategorySID VARCHAR(30))
--insert initial values
INSERT INTO @items (CategoryName, CategoryID, CategorySID)
VALUES('MILK', 5, 'MIL005'), ('JUICE', 15, 'JUI015'), ('DAIRY', 1, 'DAI001'),
('RTV', 105, 'RTV105'), ('VEGETABLES', 30, 'VEG030'), ('BREAD', 7, 'BRE007')
SELECT *
FROM @items
--Stored procedure input parameter: CategoryName
DECLARE @cn VARCHAR(30) = 'MILK'
DECLARE @cid INT
SELECT @cid = MAX(COALESCE(CategoryID,0)) +1
FROM @items
WHERE CategoryName = @cn
INSERT INTO @items (CategoryName, CategoryID, CategorySID)
SELECT @cn AS CateogryName, @cid, CONCAT(LEFT(@cn, 3), SUBSTRING('000', 1, 3-LEN(CONVERT(VARCHAR(3),@cid))), CONVERT(VARCHAR(3),@cid)) AS CategorySID
SELECT *
FROM @items
WHERE ItemID = @@IDENTITY
SET @cn = 'JUICE'
SELECT @cid = MAX(COALESCE(CategoryID,0)) +1
FROM @items
WHERE CategoryName = @cn
INSERT INTO @items (CategoryName, CategoryID, CategorySID)
SELECT @cn AS CateogryName, @cid, CONCAT(LEFT(@cn, 3), SUBSTRING('000', 1, 3-LEN(CONVERT(VARCHAR(3),@cid))), CONVERT(VARCHAR(3),@cid)) AS CategorySID
SELECT *
FROM @items
WHERE ItemID = @@IDENTITY
返回值:
1.查询 - 初始值
Returned values:
1. query - initial values
1 MILK 5 MIL005
2 JUICE 15 JUI015
3 DAIRY 1 DAI001
4 RTV 105 RTV105
5 VEGETABLES 30 VEG030
6 BREAD 7 BRE007
2.查询 - 已添加MILK项目
2. query - MILK item has been added
7 MILK 6 MIL006
3.查询 - 已添加JUICE项目
3. query - JUICE item has been added
8 JUICE 16 JUI016
如你所见,CategorySID已根据增加CategoryName
。
SP应如下所示:
As you can see, CategorySID has been increased depending on CategoryName
.
SP should looks like:
CREATE PROCEDURE InsertNewItemByCategory
@CategoryName VARCHAR(30)
AS
BEGIN
DECLARE @sid INT
SELECT @sid = MAX(COALESCE(CategoryID,0))+1
FROM ItemsTable
WHERE CategoryName = @CategoryName
--here put the code to finish SP
END
知道了吗?
Got it?
这篇关于产品自动增量的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!