-
本版专家分：1529
-
- 红花 2011年4月 其他开发语言大版内专家分月排行榜第一
-
- 蓝花 2011年3月 其他开发语言大版内专家分月排行榜第三
结帖率 98.02% -
-
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
*/
![]()
-
本版专家分：1989
-
- 铜牌 2019年5月 总版技术专家分月排行榜第三
-
-
黄花
2019年5月 Web 开发大版内专家分月排行榜第二
2019年1月 Web 开发大版内专家分月排行榜第二
-
- 蓝花 2019年4月 Web 开发大版内专家分月排行榜第三
-
-
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 行受影响)
-
版主本版专家分：102685
-
- GitHub 绑定GitHub第三方账户获取
-
- 领英 绑定领英第三方账户获取
-
- 榜眼 2017年 总版技术专家分年内排行榜第二
-
- 探花 2018年总版新获得的技术专家分排名第三
-
-
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;
