sql临时表，表变量，CTE，游标使用方法

if OBJECT_ID('groups') is not null 
    drop table groups

create table groups
(
    groupid varchar(10),
    val int null
)
insert into groups values('a','1');
insert into groups values('a','2');
insert into groups values('a','3');
insert into groups values('b','3');
insert into groups values('b','4');
insert into groups values('b','2');

--临时表
if OBJECT_ID('#tmp') is not null
drop table #tmp
go
create table #tmp
(
    groupid varchar(10),
    val int null
)
insert into #tmp select * from groups where groupid='b'
select * from #tmp

drop table #tmp
go
--临时表2
select * into #tmp from groups where groupid ='b'
select * from #tmp
drop table #tmp
go

--表变量
declare @t table(id varchar(10),val int)
insert into @t select * from groups where groupid='b'
select * from @t;

--CTE
with tb(id,value) as --alias column name
(
    select * from groups where groupid='b'
)
select * from tb

--游标
if OBJECT_ID('#tmp') is not null
drop table #tmp
go
create table #tmp
(
    groupid varchar(10),
    val int null
)
declare @id varchar(10), @val  int
declare c cursor fast_forward for select * from groups where groupid='b'
open c
fetch next from c into @id,@val
while @@FETCH_STATUS =0 
begin
    insert into #tmp values(@id,@val)
    fetch next from c into @id,@val
end
close c
deallocate c
select * from #tmp

 ※注意事项

1，CTE后面紧跟delete语句时，cte的查询语句中只能是单表，否者删除不成功。