在Sql Server 2008中，如何为每个客户的发货编号

Question

我有两个表:Customer ------>> Shipment

我想给每个客户的每批货物一个唯一的编号，例如

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

等等……

如何在sql server 2008中实现

Answer 1

要使用查询获取编号，可以这样做

select CustomerID,
       ShipmentID,
       row_number() over(partition by Shipment.CustomerID  
                         order by ShipmentID) as ShipmentNumber
from Shipment

如果你想用ShipmentNumber(新添加的列)更新一个表，你可以这样做

;with S as
(
  select ShipmentNumber,
         row_number() over(partition by Shipment.CustomerID  
                           order by ShipmentID) as ShipNum
  from Shipment
)
update S
set ShipmentNumber = ShipNum