SQL server的with的用法

with主要用法之一就是简化联合子查询的sql代码，先将要用的子查询定义成一个表，后面可以重复使用。

在多级和较为复杂的查询中可以让SQL更清晰。

简单样式1

WITH 
   subQry_A  AS (SELECT fld1, fld2, fldxxx..... FROM tabName)
SELECT * FROM subQry_A

 

简单样式2

WITH 
    subQry_A  AS (SELECT a.fld1, a.fld2, a.fldxxx..... FROM tabNameA a ) ,
    subQry_B  AS (SELECT b.fld1, b.fld2, b.fldxxx..... FROM tabNameB b )

SELECT * FROM subQry_A a
LEFT JOIN subQry_B b ON a.a_ID = b.b_ID