Normal 0 7.8 磅 0 2 false false false EN-US ZH-CN X-NONESQLServer臨時表、表變數、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

關於SQLServer2005的學習筆記——臨時表、表變數和CTE

相關文章