22,300
社区成员




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
*/
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;
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;