In Sql Server 2008,How to number shipments for each customer
I have two tables : Customer ------>> Shipment
I want to give each shipment a unique number for each customer, for example
CustomerID ShipmentID ShipmnetNumber
10 50 1
10 51 2
10 55 3
15 56 1
15 57 2
15 58 3
17 59 1
17 60 2
开发者_Python百科
etc ...
how can I do it in sql server 2008
To get the numbering with a query you can do this
select CustomerID,
ShipmentID,
row_number() over(partition by Shipment.CustomerID
order by ShipmentID) as ShipmentNumber
from Shipment
If you want to update a table with ShipmentNumber (newly added column) you can do this
;with S as
(
select ShipmentNumber,
row_number() over(partition by Shipment.CustomerID
order by ShipmentID) as ShipNum
from Shipment
)
update S
set ShipmentNumber = ShipNum
精彩评论