本文原本是为了说明游标在某些特定环境下的用途，突然发现用来说明临时表、表变量和CTE的关系更为合理一些。
本文的例子给了一串数字，是为了求得同组中所有数字的乘积，包括累计叠加等等，这样的案例通过一般的分组方式很难办到，而通过游标更容易实现。
创建一个测试表
|
CREATE TABLE groups ( groupid VARCHAR(10), val INT NULL ) INSERT INTO groups VALUES ('a',2); INSERT INTO groups VALUES ('a',3); INSERT INTO groups VALUES ('a',1); INSERT INTO groups VALUES ('b',4); INSERT INTO groups VALUES ('b',6); INSERT INTO groups VALUES ('b',1); INSERT INTO groups VALUES ('c',10); INSERT INTO groups VALUES ('c',3); INSERT INTO groups VALUES ('d',7); |
通过临时表和游标的方法加以实现
|
CREATE TABLE #result (groupid VARCHAR(10),product BIGINT);
DECLARE @groupid VARCHAR(10),@prvgroupid VARCHAR(10),@val INT,@product BIGINT; DECLARE c CURSOR FAST_FORWARD FOR SELECT groupid,val FROM groups ORDER BY groupid; OPEN c; FETCH NEXT FROM c INTO @groupid,@val; SELECT @prvgroupid=@groupid,@product=1; WHILE @@fetch_status=0 BEGIN IF @groupid<>@prvgroupid BEGIN INSERT INTO #result VALUES(@prvgroupid,@product); SELECT @prvgroupid=@groupid,@product=1; END SET @product=@product*@val; FETCH NEXT FROM c INTO @groupid,@val; END IF @prvgroupid IS NOT NULL INSERT INTO #result VALUES(@prvgroupid,@product); CLOSE c; DEALLOCATE c; SELECT groupid,product FROM #result |
通过表变量和游标的方式加以实现，从这里看的话表变量和临时表基本雷同
|
DECLARE @groupid VARCHAR(10),@prvgroupid VARCHAR(10),@val INT,@product BIGINT; DECLARE c CURSOR FAST_FORWARD FOR SELECT groupid,val FROM groups ORDER BY groupid; OPEN c; FETCH NEXT FROM c INTO @groupid,@val; SELECT @prvgroupid=@groupid,@product=1; WHILE @@fetch_status=0 BEGIN IF @groupid<>@prvgroupid BEGIN INSERT INTO #result VALUES(@prvgroupid,@product); SELECT @prvgroupid=@groupid,@product=1; END SET @product=@product*@val; FETCH NEXT FROM c INTO @groupid,@val; END IF @prvgroupid IS NOT NULL INSERT INTO #result VALUES(@prvgroupid,@product); CLOSE c; DEALLOCATE c; SELECT groupid,product FROM #result |
通过CTE的方式加以实现
|
WITH all_but_val AS ( SELECT a.groupid,a.val*b.val accum_val FROM groups a,groups b WHERE a.groupid=b.groupid and a.val>b.val ), but_val AS ( SELECT * FROM groups WHERE groupid IN (SELECT groupid FROM groups GROUP BY groupid HAVING COUNT(*)=1) ), all_val AS ( SELECT * FROM all_but_val UNION SELECT * FROM but_val ) SELECT groupid,max(accum_val) FROM all_val GROUP BY groupid |
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/6517/viewspace-629295/ ，如需转载，请注明出处，否则将追究法律责任。
