[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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!