有如下表groupid memberid string val
a 3 stral 6
a 9 stra2 7
b 2 strb1 3
b 4 strb2 7
b 5 strb3 3
b 9 strb4 11
c 3 strc1 8
c 7 strc2 10
c 9 strc3 12
想得出结果如下,注意product,是groupid相对应的val乘积
groupid product
a 42
b 693
c 960谢谢
a 3 stral 6
a 9 stra2 7
b 2 strb1 3
b 4 strb2 7
b 5 strb3 3
b 9 strb4 11
c 3 strc1 8
c 7 strc2 10
c 9 strc3 12
想得出结果如下,注意product,是groupid相对应的val乘积
groupid product
a 42
b 693
c 960谢谢
if object_id('tempdb.dbo.T') is not null drop table T
create table T (groupid varchar(1),memberid int,string varchar(5),val int)
insert into T
select 'a',3,'stral',6 union all
select 'a',9,'stra2',7 union all
select 'b',2,'strb1',3 union all
select 'b',4,'strb2',7 union all
select 'b',5,'strb3',3 union all
select 'b',9,'strb4',11 union all
select 'c',3,'strc1',8 union all
select 'c',7,'strc2',10 union all
select 'c',9,'strc3',12
go
create function dbo.f_total(@groupid varchar(1))
returns bigint
as
begin
declare @re bigint
set @re=1
select @re=@re*val
from T
where groupid=@groupid
return @re
endgo
select groupid,dbo.f_total(groupid) total
from T
group by groupiddrop table T
drop function dbo.f_total/*
groupid total
------- --------------------
a 42
b 693
c 960(3 行受影响)
*/
也可以这样子
if object_id('tempdb.dbo.T') is not null drop table T
create table T (groupid varchar(1),memberid int,string varchar(5),val int)
insert into T
select 'a',3,'stral',6 union all
select 'a',9,'stra2',7 union all
select 'b',2,'strb1',3 union all
select 'b',4,'strb2',7 union all
select 'b',5,'strb3',3 union all
select 'b',9,'strb4',11 union all
select 'c',3,'strc1',8 union all
select 'c',7,'strc2',10 union all
select 'c',9,'strc3',12
go
create function dbo.f_total(@groupid varchar(1))
returns bigint
as
begin
declare @re bigint
set @re=1
select @re=@re*val
from T
where groupid=@groupid
return @re
end
select distinct groupid,dbo.f_total(groupid) total
from T