27,582
社区成员




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 行受影响)
--> 生成测试数据表: [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 行受影响)
*/