Sql Server (With)（随手笔记）

Sql中With作用

1）with As短语，也叫做子查询部分（subquery factoring），可以定义一个SQL片断，该SQL片断会被整个SQL语句用到。可以使SQL语句的可读性更高，也可以在union All的不同部分，作为提供数据的部分。
2）对于union All，使用With As定义了一个union All语句，当该片断被调用2次以上，优化器会自动将该WITH AS短语所获取的数据放入一个Temp表中。而提示Meterialize则是强制将with As短语的数据放入一个全局临时表中。很多查询通过该方式都可以提高速度。

示例

–创建表
create table CountryRegion (id int,[name] nvarchar(50),price int,age int,CountryRegionCode nvarchar(50))
–插入数据
truncate table CountryRegion
insert into CountryRegion values (1,‘abc’,1,15,‘四川’)
insert into CountryRegion values (2,‘cba’,15,30,‘四川’)
insert into CountryRegion values (3,‘bbc’,21,23,‘重庆’)
insert into CountryRegion values (4,‘opc’,31,33,‘湖北’)
insert into CountryRegion values (5,‘avc’,91,8,‘湖北’)
insert into CountryRegion values (6,‘ipo’,71,18,‘北京’)

–创建表
create table StateProvince (id int,CountryRegionCode nvarchar(50),Company nvarchar(50))
–插入数据
truncate table StateProvince
insert into StateProvince values (1,‘四川’,‘天乐’)
insert into StateProvince values (2,‘四川’,‘顺疯’)
insert into StateProvince values (3,‘重庆’,‘KO’)
insert into StateProvince values (4,‘湖北’,‘黄鹤楼’)

1）嵌套的查询语句，虽然这条SQL语句并不复杂，但如果嵌套的层次过多，会使SQL语句非常难以阅读和维护
select * from StateProvince where CountryRegionCode in
(select CountryRegionCode from CountryRegion where Name like ‘C%’)

2）SQL语句要比第一种方式更复杂，但却将子查询放在了表变量@t中，这样做将使SQL语句更容易维护，但又会带来另一个问题，就是性能的损失。
–由于表变量实际上使用了临时表，从而增加了额外的I/O开销，因此，表变量的方式并不太适合数据量大且频繁查询的情况。为此，在SQL Server 2005中提供了另外一种解决方案，这就是公用表表达式（CTE），使用CTE，可以使SQL语句的可维护性，同时，CTE要比表变量的效率高得多。
declare @t table(CountryRegionCode nvarchar(3))
insert into @t(CountryRegionCode) (select CountryRegionCode from CountryRegion where Name like ‘C%’)
select * from StateProvince where CountryRegionCode in (select * from @t)

3）使用CTE来解决上面的问题
with
cr as
(
select CountryRegionCode from CountryRegion where Name like ‘C%’
)
select * from StateProvince where CountryRegionCode in (select * from cr)

4）CTE后面也可以跟其他的CTE，但只能使用一个with，多个CTE中间用逗号（,）分隔
with
cte1 as
(
select * from CountryRegion where name like ‘abc%’
),
cte2 as
(
select * from CountryRegion where id < 20
),
cte3 as
(
select * from CountryRegion where price < 100
)
select a.* from cte1 a, cte2 b, cte3 c where a.id = b.id and a.id = c.id