|
3>
4> IF OBJECT_ID('dbo.Groups') IS NOT NULL
5> DROP TABLE dbo.Groups;
6> GO
1>
2> CREATE TABLE dbo.Groups
3> (
4> groupid VARCHAR(10) NOT NULL,
5> memberid INT NOT NULL,
6> string VARCHAR(10) NOT NULL,
7> val INT NOT NULL,
8> PRIMARY KEY (groupid, memberid)
9> );
10>
11> INSERT INTO dbo.Groups(groupid, memberid, string, val)
12> VALUES('a', 3, 'stra1', 6);
13> INSERT INTO dbo.Groups(groupid, memberid, string, val)
14> VALUES('a', 9, 'stra2', 7);
15> INSERT INTO dbo.Groups(groupid, memberid, string, val)
16> VALUES('b', 2, 'strb1', 3);
17> INSERT INTO dbo.Groups(groupid, memberid, string, val)
18> VALUES('b', 4, 'strb2', 7);
19> INSERT INTO dbo.Groups(groupid, memberid, string, val)
20> VALUES('b', 5, 'strb3', 3);
21> INSERT INTO dbo.Groups(groupid, memberid, string, val)
22> VALUES('b', 9, 'strb4', 11);
23> INSERT INTO dbo.Groups(groupid, memberid, string, val)
24> VALUES('c', 3, 'strc1', 8);
25> INSERT INTO dbo.Groups(groupid, memberid, string, val)
26> VALUES('c', 7, 'strc2', 10);
27> INSERT INTO dbo.Groups(groupid, memberid, string, val)
28> VALUES('c', 9, 'strc3', 12);
29>
30>
31> DECLARE
32> @Result TABLE(groupid VARCHAR(10), product BIGINT);
33> DECLARE
34> @groupid AS VARCHAR(10), @prvgroupid AS VARCHAR(10),
35> @val AS INT, @product AS BIGINT;
36> DECLARE C CURSOR FAST_FORWARD FOR
37> SELECT groupid, val FROM dbo.Groups ORDER BY groupid;
38> OPEN C
39> FETCH NEXT FROM C INTO @groupid, @val;
40> SELECT @prvgroupid = @groupid, @product = 1;
41> WHILE @@fetch_status = 0
42> BEGIN
43> IF @groupid <> @prvgroupid
44> BEGIN
45> INSERT INTO @Result VALUES(@prvgroupid, @product);
46> SELECT @prvgroupid = @groupid, @product = 1;
47> END
48> SET @product = @product * @val;
49> FETCH NEXT FROM C INTO @groupid, @val;
50> END
51> IF @prvgroupid IS NOT NULL
52> INSERT INTO @Result VALUES(@prvgroupid, @product);
53> CLOSE C;
54> DEALLOCATE C;
55>
56> SELECT groupid, product FROM @Result;
57>
58> GO
groupid product
---------- --------------------
a 42
b 693
c 960
1>
2> drop table Groups;
3> GO
|