select a.KeyValue,a.whPosition,
(Sum(a.Quantity*b.NetWeightKG)- (select sum(a.Quantity*b.NetWeightKG ) from stInOut a,stPacking b where InOrOut='2' and Void='0' group by a.KeyValue,a.whPosition )) as SumKG,
(Sum(a.Quantity*b.NetWeightLB)- (select sum(a.Quantity*b.NetWeightLB ) from stInOut a,stPacking b where InOrOut='2' and Void='0' group by a.KeyValue,a.whPosition )) as SumLB
from stInOut a,stPacking b
where a.PackCode=b.PackCode and a.InOrOut='1' and a.Void='0'
group by a.KeyValue,a.whPosition
Order by a.KeyValue,a.whPosition 语法可以通过。但运行时有错误,请问如何解决?
现在贴出错误信息!
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Warning: Null value is eliminated by an aggregate or other SET operation.
(Sum(a.Quantity*b.NetWeightKG)-
(select sum(a.Quantity*b.NetWeightKG )
from stInOut a,stPacking b where InOrOut='2' and Void='0'
and a.KeyValue = KeyValue,a.whPosition = whPosition)) as SumKG,
//--上句不可用group by 因为 用了就不止一条数据了
(Sum(a.Quantity*b.NetWeightLB)-
(select sum(a.Quantity*b.NetWeightLB )
from stInOut a,stPacking b where InOrOut='2' and Void='0'
and a.KeyValue = KeyValue, a.whPosition = whPosition )) as SumLB
//--同样上句不也可用group by
from stInOut a,stPacking b
where a.PackCode=b.PackCode and a.InOrOut='1' and a.Void='0'
group by a.KeyValue,a.whPosition
Order by a.KeyValue,a.whPosition
SumKG和SumLB的查询结果都与实际的不符合。!
-26649668.0000,-28293582.6900
????
我改的可能跟你要的不同
当a.InOrOut='2' and a.Void='0' 求出sum(a.Quantity*b.NetWeightKG ) 两个不同
sum(a.Quantity*b.NetWeightKG )的差 就是SumKG。stInOut a, stPacking b
不知道你要了解的是不是这个 。。?
小d佩服 这么长的SQL也写的出来兄弟。 你走拉,别人怎么维护你的东西啊 牛b!!!!!!!!!!
-----------------------------------------这位仁兄,能否指教下。。小弟初学让你见笑了!
--你每个不同的a.KeyValue,a.whPosition
SumKG, SumLB 都要计算一次
当然慢了
你可以试一下用自联接
这个SQL运行速度快不快啊~~~~~~
--你每个不同的a.KeyValue,a.whPosition
SumKG, SumLB 都要计算一次
当然慢了
你可以试一下用自联接
==========================
自连接?请问如何使用?没用过!
感激!!
非常感激!!
where InOrOut='2' and Void='0' 查询的数据为 28294942.6900
select sum(a.Quantity*b.NetWeightLB ) from stInOut a,stPacking b
where InOrOut='2' and Void='0' 查询的数据为 26652668.0000
所以用select a.KeyValue,a.whPosition,
(Sum(a.Quantity*b.NetWeightKG)-
(select sum(a.Quantity*b.NetWeightKG ) from stInOut a,stPacking b
where InOrOut='2' and Void='0' )) as SumKG, (Sum(a.Quantity*b.NetWeightLB)-
(select sum(a.Quantity*b.NetWeightLB )
from stInOut a,stPacking b where InOrOut='2' and Void='0' )) as SumLB from stInOut a,stPacking b
where a.PackCode=b.PackCode and a.InOrOut='1' and a.Void='0'
group by a.KeyValue,a.whPosition
Order by a.KeyValue,a.whPosition查询出的结果会产生SumKG和SumLB的查询结果都与实际的不符合。!
SumKG SumLB
-28294942.2400 -26652667.0000
-28294910.8900 -26652598.0000
-28294929.1900 -26652638.0000
-28294874.6900 -26652518.0000
…………………… ………………
请问 meiqingsong(阿飛)
发生这个原因是不是删除了那里的 group by !??
where InOrOut='2' and Void='0' 查询的数据为 28294942.6900 select sum(a.Quantity*b.NetWeightLB ) from stInOut a,stPacking b
where InOrOut='2' and Void='0' 查询的数据为 26652668.0000
where InOrOut='2' and Void='0' group by a.KeyValue
这样使用了group by 才能出现多条。。!!
where InOrOut='2' and Void='0'这样查询出的是满足InOrOut='2' and Void='0' 条件的总和!
(Sum(a.Quantity*b.NetWeightKG)
-
(select sum(a.Quantity*b.NetWeightKG )
from stInOut a,stPacking b
where InOrOut='2' and Void='0'
group by a.KeyValue,a.whPosition ))
--跟a.KeyValue,a.whPosition 没有关系吗?
你要的是不是
a.KeyValue,a.whPosition 相同时
Sum(a.Quantity*b.NetWeightKG) - sum(a.Quantity*b.NetWeightKG )
--那就该是
(Sum(a.Quantity*b.NetWeightKG)
-
(select sum(a.Quantity*b.NetWeightKG )
from stInOut a,stPacking b
where InOrOut='2' and Void='0'
and a.KeyValue = KeyValue )) //---这才是 KeyValue 跟 a.KeyValue 相同时的和
Sum(a.Quantity*b.NetWeightKG)-isnull(cc.KG,0) as SumKG,
Sum(a.Quantity*b.NetWeightLB)-isnull(cc.LB,0) as SumLB
from stInOut a,stPacking b
left join (select sum(a.Quantity*b.NetWeightKG ) as KG , sum(a.Quantity*b.NetWeightLB) as LB, KeyValue
from stInOut a,stPacking b
where InOrOut='2' and Void='0'
group by a.KeyValue,a.whPosition ) cc
on a.KeyValue=cc.KeyValue
where a.PackCode=b.PackCode and a.InOrOut='1' and a.Void='0'
group by a.KeyValue,a.whPosition
Order by a.KeyValue,a.whPosition
The column prefix 'a' does not match with a table name or alias name used in the query.