关于SQLServer2005的学习笔记——临时表、表变量和CTE

SQLServer 临时表、表变量、 CTE

 

本文原本是为了说明游标在某些特定环境下的用途，突然发现用来说明临时表、表变量和 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