怎么求一列的乘积

[apxiao123321]

如 A 9
A 8
A 7
A 6
B 4
B 5
B 8
如何用sql查询得到编号为A的数值乘积 9*8*7*6

[htl258_Tony]

--> 生成测试数据表: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL
	DROP TABLE [tb]
GO
CREATE TABLE [tb] ([a] [nvarchar](10),[b] [int])
INSERT INTO [tb]
SELECT 'A','9' UNION ALL
SELECT 'A','8' UNION ALL
SELECT 'A','7' UNION ALL
SELECT 'A','6' UNION ALL
SELECT 'B','4' UNION ALL
SELECT 'B','5' UNION ALL
SELECT 'B','8'


-->SQL查询如下:

select a,b=exp(SUM(log(b))) from tb group by a
/*
a          b
---------- ----------------------
A          3024
B          160

(2 行受影响)
*/

[arthurlzp]

用cursor来做吧

SET NOCOUNT ON;

declare fa varchar(2);
declare fv int;
declare res int;

declare c1 cursor for
select fileda, val from tab1;

set res=1;

open c1;
fetch next from c1 into fa, fv

WHILE @@FETCH_STATUS = 0
begin
if fa='A' then
set res = res*fv
end if;
fetch next from c1 into fa, fv
end;
close c1;
deallocate c1;

select res

[快溜]

  
  create table tb(no varchar(1),num int)
  insert into tb 
        select 'A',8 union
        select 'A',7 union
        select 'A',6 union
        select 'B',4 union
        select 'B',5 union
        select 'B',8 
  
 create function chengji(@no varchar(1))
 returns int
 as
 begin
     declare @num int=1
     select @num=@num* num from tb where no=@no
     return @num
 end
  
 select no,dbo.chengji(no) from tb group by no     
 
 /*
 no   
---- -----------
A    336
B    160

(2 行受影响)         

[Spade_J]

[Quote=引用 1 楼 htl258 的回复:]

SQL code

--> 生成测试数据表: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([a] [nvarchar](10),[b] [int])
INSERT INTO [tb]
SELECT 'A','9' UNION ALL
SELECT 'A','8' UNION AL……
[/Quote]
学习了~~~