SQL UDF

有如下表

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
先看下自己的苯方法

USE TEST;
GO
IF OBJECT_ID('dbo.groups') IS NOT NULL
   DROP TABLE dbo.groups;
GO
CREATE TABLE dbo.groups
( groupid varchar(10) not null,
  Memberid int  not null,
  String  varchar(10) not null,
  Val      INT NOT NULL,
  Primary key(groupid,memberid)
);

INSERT dbo.groups 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

select *
from dbo.groups

DECLARE @t table(groupid varchar(10),memberid int,val int)
DECLARE @i int
SET @i=1
INSERT INTO @t  SELECT m.groupid,m.memberid,m.val
FROM dbo.groups m
WHERE not exists( SELECT * FROM dbo.groups n WHERE m.groupid=n.groupid and m.memberid<n.memberid)
while @@rowcount > 0 and @i<10
begin
set @i=@i+1
INSERT INTO @t  SELECT m.groupid,m.memberid,m.val*n.val
FROM dbo.groups m,@t n
where m.groupid=n.groupid and m.memberid<n.memberid
end

SELECT  groupid,MAX(val) as product
FROM @t
GROUP BY groupid

感觉不是一般的苯

这是别人的方法

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
   /*注意此处是一个变量赋值语句不是一个查询语句,   所以自然不输出结果.
而且变量赋值是from   t   的,   所以是循环检索t的所有记录,   每检索到一条满足条件的记录处理一次变量赋值*/
   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

最后,发现问题就出在注释部分,自己总结出来的