问题:

Suppose I have a table like the following:

tblNumbers

Numbers
4  
5
3
6

Using SET BASED approach how can I perform a multiplication so the output will be:

Output
360

N.B~ There is no hard and fast rule that there will be only four numbers, but I'd prefer the answer to be using a CTE and/or correlated subquery.

回答1:

You can use logarithms/exponents that take advantage of the mathematical fact that:

log(a*b*c...*n)=log(a)+log(b)+log(c)...+log(n)

Therefore you can use the sum function to add all the logarithms of a column, then take the exponent of that sum, which gives the aggregate multiplication of that column:

create table #tbl (val int)
insert into #tbl (val) values(1)
insert into #tbl (val) values(2)
insert into #tbl (val) values(3)
insert into #tbl (val) values(4)

select exp(sum(log(val))) from #tbl

drop table #tbl

If memory serves me right, there an edge case that needs to be taken care of... log(0) is an error.

回答2:

declare @result int
set @result = 1

select @result = @result * [number]
from tblNumber

print @result

(note that this assumes an int column and no overflow)

回答3:

Michael's result is efficient.

You can use a recursive CTE, simply define a ROW_NUMBER and self-join. But why bother, it won't be as efficient, since you need to do a table or index scan anyway.