with as 用法

http://wudataoge.blog.163.com/blog/static/80073886200961652022389/

select * from person.StateProvince where CountryRegionCode in
         (select CountryRegionCode from person.CountryRegion where Name like 'C%')

用with as 简化嵌套:

with cr as
(
    select CountryRegionCode from person.CountryRegion where Name like 'C%'
)

select * from person.StateProvince where CountryRegionCode in (select * from cr)

多个表用“，”隔开：

with
cte1 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