[SQLServer] 临时表、表变量、 CTE

骑猪看日落 2022-07-13 08:37 187阅读 0赞

本文原本是为了说明游标在某些特定环境下的用途，突然发现用来说明临时表、表变量和 CTE 的关系更为合理一些。

本文的例子给了一串数字，是为了求得同组中所有数字的乘积，包括累计叠加等等，这样的案例通过一般的分组方式很难办到，而通过游标更容易实现。

创建一个**测试**表

CREATETABLE groups

(groupid VARCHAR(10), val INT NULL )

INSERTINTO groups VALUES ('a',2);

INSERTINTO groups VALUES ('a',3);

INSERTINTO groups VALUES ('a',1);

INSERTINTO groups VALUES ('b',4);

INSERTINTO groups VALUES ('b',6);

INSERTINTO groups VALUES ('b',1);

INSERTINTO groups VALUES ('c',10);

INSERTINTO groups VALUES ('c',3);

INSERTINTO groups VALUES ('d',7);

\-- 通过临时表和游标的方法加以实现----------------------------------------------------------------------

createtable \#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;

fetchnext 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

insertinto \#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;

fetchnext 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

insertinto \#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

[SQLServer] 临时表、表变量、 CTE

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 SQLSERVER临时表引发的“锁“案

相关 sqlserver表变量

相关 sqlserver 临时表应用范例

相关 SQLSERVER触发器之临时表

相关 [SQLServer] 临时表、表变量、 CTE

相关表变量和临时表的区别

相关 SqlServer 创建变量表

相关临时表和表变量

相关临时表 Vs 表变量(转)

相关表变量与临时表的优缺点（转）