在两个表中去sum求和,怎样去掉重复值,distinct怎么无效?
SELECT distinct SUM(ISNULL(b.BG_Freight,0.00)-ISNULL(a.BG_Freight,0.00)) FROM BA_Bargain a LEFT JOIN BG_Bargain b ON a.BGID=b.BGID AND a.BG_OP_Time>='2010-09-26' AND a.BG_OP_Time<'2010-10-26' AND b.BG_TNode='100625' AND BG_BG_Type=2
我想知道它到底变更了多少,怎么这样去重复值去不掉,还是把重复值计算进去了?
还是以这种形式:(10+10+30+40)-(15+30+40)
正确的算法应该是:(10+30+40)-(15+30+40)
BA_Bargain(变更原始表)
BGID , BG_Freight (变更前的运费) , BG_OP_Time
001 , 10 2010-09-26 01:00:00 (第一次)
001 , 10 2010-09-26 01:59:59 ( 第二次,然而没有变化)
002 , 30 2010-09-26 02:33:00
003 , 40 2010-09-26 03:00:00
BG_Bargain(变更后表)
BGID , BG_Freight(变更后的运费)
001 , 15
002 , 30
003 , 40