首页 > 代码库 > MSSQL 临时表和公用表使用案例
MSSQL 临时表和公用表使用案例
1、临时表:
1.1)实例1
if(OBJECT_ID(‘tempdb..#a‘) IS NOT NULL) drop table #a;if(OBJECT_ID(‘tempdb..#b‘) IS NOT NULL) drop table #b;SELECT name into #a from syscolumns a with(nolock) where id=OBJECT_ID(‘NewsLetterSystem_Subscriber‘);SELECT name into #b from syscolumns b where id=OBJECT_ID(‘tmpContact_130828005535769_5243_f0b7‘);select * from #a,#bwhere #a.name=#b.name;
1.2) 实例2
if(OBJECT_ID(‘tempdb..#a‘) IS NOT NULL) drop table #a;select * into #a from Categories;select * from #a;
2、公用表:
2.1)实例1
withcr as( select CountryRegionCode from person.CountryRegion where Name like ‘C%‘)select * from person.StateProvince where CountryRegionCode in (select * from cr)--其中cr是一个公用表表达式,该表达式在使用上与表变量类似
2.2) CTE后面必须直接跟使用CTE的SQL语句(如select、insert、update等),否则,CTE将失效。如下面的SQL语句将无法正常使用CTE:
withcr as( select CountryRegionCode from person.CountryRegion where Name like ‘C%‘)select * from person.CountryRegion -- 应将这条SQL语句去掉-- 使用CTE的SQL语句应紧跟在相关的CTE后面 --select * from person.StateProvince where CountryRegionCode in (select * from cr)
2.3)CTE后面也可以跟其他的CTE,但只能使用一个with,多个CTE中间用逗号(,)分隔,如下面的SQL语句所示:
withcte1 as( select * from table1 where name like ‘abc%‘),cte2 as( select * from table2 where id > 20),cte3 as( select * from table3 where price < 100)select a.* from cte1 a, cte2 b, cte3 c where a.id = b.id and a.id = c.id
2.4)实例4
with ta as ( SELECT name from syscolumns a with(nolock) where id=OBJECT_ID(‘NewsLetterSystem_Subscriber‘)),tb as(SELECT name from syscolumns b where id=OBJECT_ID(‘tmpContact_130828005535769_5243_f0b7‘))select * from ta,tb where ta.name=tb.name
MSSQL 临时表和公用表使用案例
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。