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