|
本帖最后由 xpql 于 2022-5-21 13:39 编辑
两张表需要对比进行多表“数据透视“”,用了SQL语句,始终卡在<发货表>有一项【单据类型】,因为<订单表>中没有,所以命令自动填充导致数据汇总不正确,已经标黄色。
求助内容:
请老手看下,黄色标注处是不应该有数据,学艺不精无法自行解决,求教正确的设置语句。
以下是现在设置的内容:
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.产品编码
新增红色部份
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.单据类型 = "自营退货单"
|
|