sql语句优化问题

dglyb1975 · 发表于 2012-8-19 17:52

select 日期,厂商,sum(金额) as 进货金额,0 as 退货金额,0 as 已付金额 from [进货表$] where 厂商<>null group by 日期,厂商 order by 厂商,日期 union all select 日期,厂商,0 as 进货金额,sum(金额) as 退货金额,0 as 已付金额 from [退货表$] where 厂商<>null group by 日期,厂商 order by 厂商,日期 union all select 日期,厂商,0 as 进货金额,0 as 退货金额,sum(金额) as 已付金额 from [已付表$] where 厂商<>null group by 日期,厂商 order by 厂商,日期怎么简化一下

suwenkai · 发表于 2012-8-19 18:02

没有文件怎么优化啊

suwenkai · 发表于 2012-8-19 18:04

乱做一通。

select 日期,厂商,sum(进货金额) as 进货金额,sum(退货金额) as 退货金额,sum(已付金额) as 已付金额 from (
select 日期,厂商,sum(金额) as 进货金额,0 as 退货金额,0 as 已付金额 from [进货表$]
union all
select 日期,厂商,0 as 进货金额,sum(金额) as 退货金额,0 as 已付金额 from [退货表$]
union all
select 日期,厂商,0 as 进货金额,0 as 退货金额,sum(金额) as 已付金额 from [已付表$]
) where 厂商 is not null group by 日期,厂商 order by 厂商,日期

复制代码

amazeyeli · 发表于 2012-8-19 18:20

[code=sql]SELECT A.日期,A.厂商,sum(A.金额) as 进货金额,sum(B.金额) as 退货金额,sum(C.金额) as 已付金额
FROM [进货表$] AS A
LEFT JOIN [退货表$] AS B
ON A.厂商 = B.厂商
LEFT JOIN [已付表$] AS C
ON A.厂商 = C.厂商
group by A.日期,A.厂商
order by 厂商,日期[/code]

		自动登录	找回密码
密码			免费注册

sql语句优化问题

评分

评分