sql临时表,表变量,CTE,游标使用方法
2024-08-28 09:25:15
if OBJECT_ID('groups') is not null
drop table groups create table groups
(
groupid varchar(10),
val int null
)
insert into groups values('a','');
insert into groups values('a','');
insert into groups values('a','');
insert into groups values('b','');
insert into groups values('b','');
insert into groups values('b',''); --临时表
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的查询语句中只能是单表,否者删除不成功。
最新文章
- hihoCoder#1014
- IIS7.5 webapi 不支持 Delete、Put 解决方法
- Atitit.在线充值功能的设计
- Thread 同步线程(打印机同步)
- LR录制https协议报证书错误,导航已阻止
- AOJ 740 求和
- inline-block 和 float 的区别
- 递归获取字符串内的所有图片src地址
- [CentOS]CentOS/RedHat/Fedora的Proxy设定(yum,wget,,rpm)
- NRPE: Unable to read output 问题处理总结
- ";Storage Virtualization"; VS ";Software-Defined Storage";
- jquery插件autocomplete
- TableView cell自适应高度-----xib
- SSH网上商城---需求分析+表关系分析
- 玩转PHP(二)--PHP强大的时间函数:date()
- Map的isEmpty()与==null的区别
- 【BZOJ 2721】 2721: [Violet 5]樱花 (筛)
- 2008技术内幕:T-SQL语言基础 单表查询摘记
- 阿里云服务器ECS按ctrl+alt+delete无法登录
- 20155207 实验四 《Android程序设计》实验报告