---汇总并计算数据,通过他们共同属性字段时间汇总计算数据得到结果
SELECT a.oprate_time,Convert(decimal(18,2),ISNULL((a.oprate_amount+ISNULL(b.oprate_amount,0)),0)) as hasMoney,Convert(decimal(18,2),ISNULL(b.oprate_amount,0)) as cancelPaid,Convert(decimal(18,2),ISNULL(c.oprate_amount,0)) as outPaid,Convert(decimal(18,2),ISNULL(d.oprate_amount,0)) as refundPaid,Convert(decimal(18,2),ISNULL((c.oprate_cbj-d.oprate_cbj),0)) as tatcb,(Convert(decimal(18,2),ISNULL(c.oprate_amount,0))-Convert(decimal(18,2),ISNULL(d.oprate_amount,0))-Convert(decimal(18,2),ISNULL((c.oprate_cbj-d.oprate_cbj),0))) as ml,ISNULL((Convert(decimal(18,2),(Convert(decimal(18,2),ISNULL(c.oprate_amount,0))-Convert(decimal(18,2),ISNULL(d.oprate_amount,0))-Convert(decimal(18,2),ISNULL((c.oprate_cbj-d.oprate_cbj),0)))/(c.oprate_amount-d.oprate_amount),2)),0) as mll,ISNULL((Convert(decimal(18,2),(CASE WHEN b.oprate_amount*(a.oprate_amount+ISNULL(b.oprate_amount,0)) =0 THEN 0 ELSE b.oprate_amount/(a.oprate_amount+ISNULL(b.oprate_amount,0)) end),2)),0) as cancelOrder,ISNULL((Convert(decimal(18,2),(CASE WHEN d.oprate_amount*(a.oprate_amount+ISNULL(b.oprate_amount,0)) =0 THEN 0 ELSE d.oprate_amount/(a.oprate_amount+ISNULL(b.oprate_amount,0)) end),2)),0) as refundOrder,ISNULL((Convert(decimal(18,2),(CASE WHEN c.oprate_amount*(a.oprate_amount+ISNULL(b.oprate_amount,0)) =0 THEN 0 ELSE c.oprate_amount/(a.oprate_amount+ISNULL(b.oprate_amount,0)) end),2)),0) as outOrder
---查询类型为A的数据根据时间汇总并且把它当成一张表
FROM (SELECT a.oprate_time,sum(a.oprate_amount) as oprate_amount,sum(a.oprate_cbj) as oprate_cbj FROM (SELECT DISTINCT a.shop_no,a.oprate_time,a.oprate_amount,a.oprate_cbj,a.oprate_code,a.order_no,a.spec_no,a.spec_no_sale FROM dbo.oprate_data a where a.oprate_code='A' )a GROUP BY a.oprate_time)a
LEFT JOIN
--- 查询取消金额查询类型为B的数据根据时间汇总并且把它当成一张表
(SELECT count(1) as orderNum ,a.oprate_time,sum(a.oprate_amount) as oprate_amount,sum(a.oprate_cbj) as oprate_cbj FROM (SELECT DISTINCT a.shop_no,a.oprate_time,a.oprate_amount,a.oprate_cbj,a.oprate_code,a.order_no FROM dbo.oprate_data a where a.oprate_code='B' )a GROUP BY a.oprate_time)b
on a.oprate_time=b.oprate_time
----查询出库订单金额查询类型为C的数据根据时间汇总并且把它当成一张表
LEFT JOIN
(SELECT count(1) as orderNum ,a.oprate_time,sum(a.oprate_amount) as oprate_amount,sum(a.oprate_cbj) as oprate_cbj FROM (SELECT DISTINCT a.shop_no,a.oprate_time,a.oprate_amount,a.oprate_cbj,a.oprate_code,a.order_no FROM dbo.oprate_data a where a.oprate_code='C' )a GROUP BY a.oprate_time)c
on b.oprate_time=c.oprate_time
---查询销售退货金额查询类型为D的数据根据时间汇总并且把它当成一张表
LEFT JOIN
(SELECT count(1) as orderNum ,a.oprate_time,sum(a.oprate_amount) as oprate_amount,sum(a.oprate_cbj) as oprate_cbj FROM (SELECT DISTINCT a.shop_no,a.oprate_time,a.oprate_amount,a.oprate_cbj,a.oprate_code,a.order_no FROM dbo.oprate_data a where a.oprate_code='D' )a GROUP BY a.oprate_time)d
on c.oprate_time = d.oprate_time ORDER BY a.oprate_time desc
最后欢迎各位同学交流指导... ...