背景：ERP 首页，亚马逊销售平台单价 为 订单销售价/ 订单量 ，但实际订单销售价已含税价格，基于业务成本贴近真实考虑，调整单价 - 税费；
需求：
现状，亚马逊客单价 = 订单销售价（含税）/ 非零订单数量
亚马逊平台销售额 = 订单销售价（含税）* 【（ 非零订单数量 + 零订单数量 ）并且 非备库单 并且 非作废订单 】 - 亚马逊税费（合计）
所有平台汇总销售价 = 亚马逊平台销售额 + 其他平台销售额
调整，亚马逊客单价 = （订单销售价 - 税费）/ 非零订单数量
亚马逊平台销售额 = 订单销售价（已扣税）* 【（ 非零订单数量 + 零订单数量 ）并且 非备库单 并且 非作废订单 】
所有平台汇总销售价 = 亚马逊平台销售额 + 其他平台销售额
影响：如下截图，所有平台汇总价格 ，亚马逊客单价，亚马逊平台销售额，
APP 销售额，APP 客单价，APP 平台汇总销售额。
4、计算税费的语句
SELECT SUM
( ol.ActivityMinusPrice/ oi.ExchangeUSD )
FROM
RB_OrderList ol
LEFT JOIN RB_OrderInfo oi on ol.OrderCode=oi.OrderCode
WHERE
ol.CustomerID = 1
AND ol.OrderCode IN (
SELECT
oi.OrderCode
FROM
RB_OrderInfo oi WITH ( nolock )
LEFT JOIN RB_OrderSource os ON oi.CustomerID= os.CustomerID
AND oi.OrderSourceID= os.ID
WHERE
os.OrderSourceType= 1
AND oi.OrderStatus!= 2
AND oi.IsStockOrder= 0
AND ( oi.AddTime>= '2022-01-12 00:00:00' AND oi.AddTime< '2022-01-13 00:00:00' )
AND oi.IsSystem= 0
AND (( IsPay = 1 AND IsFBAOrder = 0 ) OR IsFBAOrder = 1 )
AND ( oi.DownLoadUpdateTime <> '2000-01-01' OR oi.TotalPrice<> 0 )
AND oi.TotalPrice<> 0
)
-
1、计算订单数据（包含订单金额为0的）
SELECT COUNT ( * ) as '订单量'
FROM
RB_OrderInfo oi WITH ( nolock )
LEFT JOIN RB_OrderSource os ON oi.CustomerID= os.CustomerID
AND oi.OrderSourceID= os.ID
WHERE
os.CustomerID=1938
AND os.OrderSourceType=1
AND oi.OrderStatus!= 2
AND oi.IsStockOrder= 0
AND ( oi.AddTime >= '2022-01-13 00:00:00' AND oi.AddTime<='2022-01-14 00:00:00' )
AND oi.IsSystem= 0
AND (( IsPay = 1 AND IsFBAOrder = 0 ) OR IsFBAOrder = 1 )
AND (oi.DownLoadUpdateTime <>'2000-01-01' OR oi.TotalPrice<>0)
-
2、订单数量（不包含订单金额为0的）
SELECT COUNT ( * ) as '订单量'
FROM
RB_OrderInfo oi WITH ( nolock )
LEFT JOIN RB_OrderSource os ON oi.CustomerID= os.CustomerID
AND oi.OrderSourceID= os.ID
WHERE
os.CustomerID=1938
AND os.OrderSourceType=1
AND oi.OrderStatus!= 2
AND oi.IsStockOrder= 0
AND ( oi.AddTime >= '2022-01-13 00:00:00' AND oi.AddTime<='2022-01-14 00:00:00' )
AND oi.IsSystem= 0
AND (( IsPay = 1 AND IsFBAOrder = 0 ) OR IsFBAOrder = 1 )
AND (oi.DownLoadUpdateTime <>'2000-01-01' OR oi.TotalPrice<>0)
AND oi.TotalPrice<>0
-
3、订单总额
SELECT SUM
( oi.TotalPrice/ExchangeUSD )
FROM
RB_OrderInfo oi WITH ( nolock )
LEFT JOIN RB_OrderSource os ON oi.CustomerID= os.CustomerID
AND oi.OrderSourceID= os.ID
WHERE
oi.CustomerID=1938
AND os.OrderSourceType= 1
AND oi.OrderStatus!= 2
AND oi.IsStockOrder= 0
AND ( oi.AddTime>= '2022-01-13 00:00:01' AND oi.AddTime< '2022-01-14 00:00:00' )
AND oi.IsSystem= 0
AND (( IsPay = 1 AND IsFBAOrder = 0 ) OR IsFBAOrder = 1 )
AND (
oi.DownLoadUpdateTime <> '2000-01-01'
OR oi.TotalPrice<> 0)
销售平台
销售平台 销售平台销售平台