产品自动增量 [英] Productwise Auto Increment

问题描述

亲爱的所有



请告诉我。



如何制作自动增量产品明智的商品代码数字



我必须将记录保存到数据库，因为我有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

推荐答案

您将无法使用自动增量;你必须实现自己的逻辑。

添加某种类别表可能会有所帮助。

如果您使用的是SQL Server 2012，您可能需要查看序列。

You won't be able to use auto-increment; you will have to implement your own logic.
Adding some sort of category table will probably help.
If you are using SQL Server 2012 you may want to look into Sequences.

您可以创建存储过程 [ ^ ]（如何创建SP [ ^ ]）实现这一点。



在我们开始编写SP之前，请看一下示例：

You can create stored procedure[^] (How to create SP[^]) to achieve that.

Before we start writing SP, have a look at example:

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?

这篇关于产品自动增量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！