求个sql,相同单号只取一条费用

[水向东流时光怎么偷]

SELECT e.Carrier_Name,
       a.OrderNumber,
       SUM(b.TranslationsCost + b.TranslationsCostTP + b.ReturnBoxCost + b.PickupCost + b.LoadinCost + b.DeliveryCost
           + b.UpstairsCost + b.HandlingCost + b.VentingCost + b.OvernightCost + b.ShortCost + b.OtherCost
           + b.OtherCost1 + b.DeductionCost
          ) AS ReSumCost
FROM dbo.TMS_Order a
    JOIN dbo.TMS_Order_Receivable b
        ON a.OrderNumber = b.orderNumber
    JOIN dbo.TMS_OrderSendTruckList c
        ON a.OrderNumber = c.OrderNumber
    JOIN dbo.TMS_OrderSendTruck d
        ON c.SendTruck_Number = d.SendTruck_Number
    JOIN dbo.TMS_Base_Carrier e
        ON d.Carrier_Guid = e.Carrier_Guid
GROUP BY e.Carrier_Name,
         a.OrderNumber
ORDER BY a.OrderNumber;
/*Carrier_Name	OrderNumber	ReSumCost
临时	JOY19080300003	170
张三	JOY19080300003	170
临时	JOY19080300004	196.5
张三	JOY19080300004	196.5
临时	JOY19080300006	458.8
张三	JOY19080300006	458.8
李四	JOY19080300007	272
李四	JOY19080300008	315
临时	JOY19080300008	315
*/

相同OrderNumber只取一条ReSumCost到出以下结果
/*Carrier_Name	OrderNumber	ReSumCost
临时	JOY19080300003	170
张三	JOY19080300003	0
临时	JOY19080300004	196.5
张三	JOY19080300004	0
临时	JOY19080300006	458.8
张三	JOY19080300006	0
李四	JOY19080300007	272
李四	JOY19080300008	315
临时	JOY19080300008	0
*/

[二月十六]

SELECT Carrier_Name,
       OrderNumber,
       CASE
            WHEN ROW_NUMBER() OVER (PARTITION BY ReSumCost ORDER BY GETDATE()) = 1 THEN ReSumCost
            ELSE 0 END ReSumCost
  FROM (   SELECT e.Carrier_Name,
                  a.OrderNumber,
                  SUM(b.TranslationsCost + b.TranslationsCostTP + b.ReturnBoxCost + b.PickupCost + b.LoadinCost
                      + b.DeliveryCost + b.UpstairsCost + b.HandlingCost + b.VentingCost + b.OvernightCost
                      + b.ShortCost + b.OtherCost + b.OtherCost1 + b.DeductionCost) AS ReSumCost
             FROM dbo.TMS_Order a
             JOIN dbo.TMS_Order_Receivable b
               ON a.OrderNumber      = b.orderNumber
             JOIN dbo.TMS_OrderSendTruckList c
               ON a.OrderNumber      = c.OrderNumber
             JOIN dbo.TMS_OrderSendTruck d
               ON c.SendTruck_Number = d.SendTruck_Number
             JOIN dbo.TMS_Base_Carrier e
               ON d.Carrier_Guid     = e.Carrier_Guid
            GROUP BY e.Carrier_Name,
                     a.OrderNumber) T
 ORDER BY OrderNumber;

[Hello World,]

DECLARE @t TABLE(Carrier_Name NVARCHAR(10) NOT NULL,
                 OrderNumber VARCHAR(20) NOT NULL,
                 ReSumCost DECIMAL(10, 2) NOT NULL);

INSERT  @t(Carrier_Name, OrderNumber, ReSumCost)
VALUES('临时', 'JOY19080300003', 170),
      ('张三', 'JOY19080300003', 170),
      ('临时', 'JOY19080300004', 196.5),
      ('张三', 'JOY19080300004', 196.5),
      ('临时', 'JOY19080300006', 458.8),
      ('张三', 'JOY19080300006', 458.8),
      ('李四', 'JOY19080300007', 272),
      ('李四', 'JOY19080300008', 315),
      ('临时', 'JOY19080300008', 315);

SELECT  *,
        CASE WHEN LAG(OrderNumber, 1) OVER (PARTITION BY OrderNumber ORDER BY OrderNumber) = OrderNumber THEN
                 0
            ELSE ReSumCost
        END NewReSumCost
FROM    @t;

Carrier_Name OrderNumber ReSumCost NewReSumCost
------------ -------------------- --------------------------------------- ---------------------------------------
临时 JOY19080300003 170.00 170.00
张三 JOY19080300003 170.00 0.00
临时 JOY19080300004 196.50 196.50
张三 JOY19080300004 196.50 0.00
临时 JOY19080300006 458.80 458.80
张三 JOY19080300006 458.80 0.00
李四 JOY19080300007 272.00 272.00
李四 JOY19080300008 315.00 315.00
临时 JOY19080300008 315.00 0.00

(9 行受影响)