sql解惑,该怎样写,已写一个感觉不好

[dabster1]

有如下表

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

谢谢

[liangCK]

写函数..

[liangCK]

--> 测试数据: #T
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

go
select groupid,dbo.f_total(groupid) total
from T
group by groupid

drop table T
drop function dbo.f_total

/*
groupid total
------- --------------------
a       42
b       693
c       960

(3 行受影响)
*/

[czvos]

收藏先

[yyixin]

标记

[hery2002]

[Quote=引用 1 楼 liangCK 的回复:]
写函数..
[/Quote]
如2楼.

[cjjsun]

顶！

[zhiguo2008]

[Quote=引用 1 楼 liangCK 的回复:]
写函数..
[/Quote]

也可以这样子

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

[victory610]

楼主写的好极了，学习了。

[gkl0818]

学习了～～～｀

[SQLnewlearner]

[Quote=引用 8 楼 victory610 的回复:]
楼主写的好极了，学习了。
[/Quote]

[lff642]

写个函数比较方便，因为你的返回值，只是一个值而以。

[lff642]

2楼和7楼的算法都是一样的。