求一个列相乘的SQL语句

[pingxuan1987]

各位好，我想让一个列的所有值相乘，请问SQL应该要怎么写？注：是列的自相乘哦。

[王向飞]

测试数据、期望结果

[黄_瓜]

--> 测试数据：#tb
if object_id('tempdb.dbo.#tb') is not null drop table #tb
create table #tb([id] int)
insert #tb
select 1 union all
select 2 union all
select 3


--1 
declare @i bigint
select @i=isnull(@i,1)*[id] from #tb
select @i
--2

[叶子]

DECLARE @table TABLE(id int)
INSERT INTO @table
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT  4 UNION ALL
SELECT 5

DECLARE @i INT
SET @i=1
SELECT @i=@i*id FROM @table
SELECT @i
/*
120
*/

[黄_瓜]

--> 测试数据：#tb
if object_id('tempdb.dbo.#tb') is not null drop table #tb
create table #tb([id] int)
insert #tb
select 1 union all
select 2 union all
select 3 


--1 
declare @i bigint
select @i=isnull(@i,1)*[id] from #tb
select @i
--2
select POWER(10,sum(log10(id))) from #tb
/*

-----------
6

(1 行受影响)


*/

[叶子]

[Quote=引用 2 楼 beirut 的回复:]
SQL code
--> 测试数据：#tb
if object_id('tempdb.dbo.#tb') is not null drop table #tb
create table #tb([id] int)
insert #tb
select 1 union all
select 2 union all
select 3


--1
declare @i bigint……
[/Quote]
思路和小爱一样，但是小爱写的比我的完善。

1、小爱考虑到了null的情况，null*任何值为null
2、小爱用的是bigint，当值多大的时候，溢出的比较晚

赞一下

[叶子]

[Quote=引用 4 楼 beirut 的回复:]
SQL code
--> 测试数据：#tb
if object_id('tempdb.dbo.#tb') is not null drop table #tb
create table #tb([id] int)
insert #tb
select 1 union all
select 2 union all
select 3


--1
declare @i bigin……
[/Quote]
小爱的第二种方式思路很巧妙
为什么我计算结果不一样，求教

DECLARE @table TABLE(id int)
INSERT INTO @table
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5

select POWER(10,sum(log10(id))) from @table

/*
119
*/

[Shawn]

--#1
DECLARE @I BIGINT
SET @I = 1
SELECT @I=@I*ISNULL([ID],1) FROM tb
SELECT @I
--#2
DECLARE @sql VARCHAR(8000)
SET @sql = '1'
SELECT @sql = @sql + '*' + CAST(ISNULL([ID],1) AS VARCHAR(100)) FROM #tb
SET @sql = 'select ' + @sql
EXEC(@sql)

[黄_瓜]

--> 测试数据：#tb
if object_id('tempdb.dbo.#tb') is not null drop table #tb
create table #tb([id] int)
insert #tb
select 1 union all
select 2 union all
select 3 union all
select 0

--1 
declare @i bigint
select @i=isnull(@i,1)*[id] from #tb
select @i
--这个分两种情况，忽略0或者不忽略0
select POWER(10,sum(log10(case when id =0 then null else id end))) from #tb
/*

-----------
6
警告: 聚合或其他 SET 操作消除了 Null 值。

(1 行受影响)


*/
--如果列中有0即返回0
select POWER(10,case when  exists(select 1 from #tb where id=0) then -1
                     else sum(log10(case when id =0 then null else id 
                     end))
                     end)
 from #tb
/*

-----------
0

(1 行受影响)


*/

[黄_瓜]

[Quote=引用 6 楼 maco_wang 的回复:]
引用 4 楼 beirut 的回复:
SQL code
--> 测试数据：#tb
if object_id('tempdb.dbo.#tb') is not null drop table #tb
create table #tb([id] int)
insert #tb
select 1 union all
select 2 union all
select 3


--1……
[/Quote]
精度问题，log10返回的float类型

[tsinghua84]

--SQL中实现相乘
--数学逻辑:
--e^A = B,则lnB = A,e^(lnB) = B
--a*b*c*d = e^(lna) * e^(lnb) * e^(lnc) * e^(lnd) = e^(lna+lnb+lnc+lnd)= e^(SUM(ln(column1)))
--=EXP(SUM(LOG(Column1)

CREATE TABLE t_EXP_LOG
(
	col1 VARCHAR(20) NOT NULL,
	col2 DECIMAL(10,3) NOT NULL
)

INSERT INTO t_EXP_LOG VALUES('a1',0.9)
INSERT INTO t_EXP_LOG VALUES('a1',0.8)
INSERT INTO t_EXP_LOG VALUES('a2',0.7)
INSERT INTO t_EXP_LOG VALUES('a2',0.6)

SELECT col1,EXP(SUM(LOG(col2))) AS yr
FROM t_EXP_LOG
GROUP  BY col1

a1	0.72
a2	0.42

DROP TABLE t_EXP_LOG

[学学你们]

遍历相乘？