sum(c1)*sum(c2)和sum(c1*c2)之间的差异

Question

在SQL语言中，有什么不同的结果或性能

sum(c1) * sum(c2) from t

和

sum(c1 * c2) from t

我用它来汇总销售总额。

select sum(price * quantity) as total from Bill

那么什么是最好的选择呢？

Answer 1

垂直和水平的总结..。假设没有空档

declare @mytable table (a int, b int)

insert into @mytable 
values 
(1,1),
(2,3)


select * from @mytable

select sum(a) * sum(b) from @mytable  -- result is 12 .. vertical/column, summarize column first then multiply to other column

select sum(a*b) from @mytable  --- result is 7  .. horizontal/row, summarize the product of a and b

Answer 2

这不是SQL的问题。只是基本的数学。假设c1=(0,1)和c2=(2,3)。然后：

sum(c1)*sum(c2) alias... (0+1)*(2+3) = 5

不与相同吗：

sum(c1*c2)      alias... (0*2)+(1*3) = 3

Answer 3

零和零是一个关注点。

Declare @YourTable table (C1 money,C2 money)
Insert Into @YourTable values
 (100,10)
,(100,null)

Select sum(c1) * sum(c2)   -- 2000.00
 From @YourTable

Select sum(c1 * c2)        -- 1000.00
 From @YourTable