|
本帖最后由 xpql 于 2022-5-21 13:40 编辑
两表利用SQL对比,在【订单表】最后三行中有【发货表】不存在的数据项无法在透视表中显示,应该是SQL语句使用了left join导致,由于Excel但无法使用full join。
需要解决的问题:
将两表进行全连接对比,数据透视表中需要显示两表的所有项,避免数据显现有遗漏。
目前语句如下:
select t1.*,t1.发货金额 * t1.发货数量 as 发货总额,
t2.订单数量,t2.订单金额,t2.订单金额 * t2.订单数量 as 订单总额
from (
select 客户名称,款号,品名,单据类型,
sum(数量) as 发货数量,
sum(金额) as 发货金额
from [发货$]
group by 客户名称,款号,品名,单据类型
) t1 left join (
select 客户,产品编码,
sum(数量) as 订单数量,
sum(金额) as 订单金额
from [订单$]
group by 客户,产品编码
) t2
on t1.客户名称 = t2.客户 and t1.款号 = t2.产品编码
where t1.单据类型 = "自营调拨单"
union all
select t1.*,0,0,0,0
from (
select 客户名称,款号,品名,单据类型,
sum(数量) as 发货数量,
sum(金额) as 发货金额
from [发货$]
group by 客户名称,款号,品名,单据类型
) t1
where t1.单据类型 = "自营退货单"
|
|