754
社区成员




SELECT a.车牌号,a.司机,
isnull(c.税额,0) as 税额,
isnull(a.运费,0) as 运费,
isnull(b.补偿费用,0) as 补偿费用,
isnull(b.扣除费用,0) as扣除费用 ,
(isnull(a.运费,0) + isnull(b.补偿费用,0) - isnull(b.扣除费用,0)) * (1 - isnull(c.税额,0)) as 实际运费
from
(select 车牌号,司机, sum(运费) as 运费 from 表A where 出货时间 between :rq1 and :rq2 GROUP BY 车牌号,司机) a
left join
(select 车牌号, sum(补偿费用) as 补偿费用, sum(扣除费用) as 扣除费用 from 表B where 调整时间 between :rq1 and :rq2 GROUP BY 车牌号) b
on a.车牌号 = b.车牌号
left join 表C c
on a.车牌号 = c.车牌号
order by a.车牌号
/*
结果中不是应该也包含CCC吗
车牌号 司机 税额 运费 补偿费用 as扣除费用 实际运费
----- ---------- -------------------- ----------- ----------- ----------- --------------------------------
aaa 小赵 .051 500 180 180 474.500
bbb 小李 .053 400 0 0 378.800
ccc 小王 .051 600 0 0 569.400
*/
SELECT a.车牌号,a.司机,
isnull(c.税额,0) as 税额,
isnull(a.运费,0) as 运费,
isnull(b.补偿费用,0) as 补偿费用,
isnull(b.扣除费用,0) as扣除费用 ,
isnull((a.运费 + b.补偿费用 - b.扣除费用) * (1 - c.税额),0) as 实际运费
from
(select 车牌号,司机, sum(运费) as 运费 from 表A where 出货时间 between :rq1 and :rq2 GROUP BY 车牌号,司机) a
left join
(select 车牌号, sum(补偿费用) as 补偿费用, sum(扣除费用) as 扣除费用 from 表B where 调整时间 between :rq1 and :rq2 GROUP BY 车牌号) b
on a.车牌号 = b.车牌号
left join 表C c
on a.车牌号 = c.车牌号
order by a.车牌号