SQL 关于单个表多个不同类型关联查询并计算

---汇总并计算数据，通过他们共同属性字段时间汇总计算数据得到结果

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

最后欢迎各位同学交流指导... ...