I want to generate Item Numbering in SQL like this:
tblOrderDetails
| OrderDetailID (Primary key) | OrderID | ItemNo | Product |
| ------------ | ------------ | ------------ | ------------ |
| 1000 | 90 | 1 | Bread |
| 1001 | 90 | 2 | Lemons |
| 1002 | 90 | 3 | Ham |
| 1003 | 91 | 1 | Chicken breast |
| 1004 | 91 | 2 | Rice |
| 1005 | 92 | 1 | Fish |
| 1006 | 92 | 2 | Potatoes |
The goal is to be able to show item numbers for each order, starting from 1. The customers can then refer to the items by their numbers, for example, "Hey, can you check the delivery status for item no. 2 in my order?" Another benefit is that the employees can sort the items in any way they want.
I want to automatically add the item number when adding a new record in Microsoft SQL Server. For example, if there are 2 items in this order (numbered 1, 2), I want to assign 3 to the new item. Then I want to be able to move items and the numbering, keeping it clean.
I created this constraint for it:
ALTER TABLE tblOrderDetails
ADD CONSTRAINT UQ_tblOrderDetails_ItemOrder UNIQUE (OrderID, ItemNo)What I don't know is how to technically set this up. How should I autogenerate numbers like I described?
EDIT AFTER QUESTION CLOSURE:
The solution provided by a moderator: MERGE with Sequential Numbering allows me to display item numbers like this:
SELECT PurchaseOrderDetailID,
PurchaseOrderID,
ProductID,
ROW_NUMBER() OVER (PARTITION BY PurchaseOrderID ORDER BY PurchaseOrderDetailID) AS ItemOrder
FROM tbl1PurchaseOrderDetailsBut this does NOT help me rearrange the items on the front end (move item up/down). I believe that the data needs to be stored physically in the table, and not just generate it in a view via ROW_NUMBER().
1条答案
按热度按时间xpcnnkqh1#
You're right that your line item numbers need to be physical columns in your table, especially if you will change the order of items or remove them.
You're not going to be able to create these line item numbers automatically with IDENTITY columns, constraints, and weird SQL incantations.
You could write a stored procedure for inserting line items that would assign the item number. This would be in a transaction that looked for the maximum existing number for the order in question and assigned the next one.
A trigger will probably not work, as it will have to do a multirow read to do each write.
But you may find it easier to handle this valid requirement in your application layer rather than your database layer. That is, have your software come up with the correct line item number as it INSERTs each row.