WITH AS短语，也叫做子查询部分（subquery factoring）

可以让你做很多事情，定义一个SQL片断，该SQL片断会被整个SQL语句所用到。

作为提供数据的部分。

代码例子：

 1 with temp as
 2  (select ID, Type_Name, Type_ID
 3     from T_Base_GoodsType as t
 4    where t.Shop_ID = @shop_id
 5      and Type_ID = @Goods_TypeID
 6   union all
 7   select t1.ID, t1.Type_Name, t1.Type_ID
 8     from T_Base_GoodsType as t1
 9    inner join temp
10       on t1.ParentType_ID = temp.Type_ID
11    where t1.Shop_ID = @shop_id)
12 select *
13   from (select Stock_Amount,
14                S.StockWarn_Amount,
15                S.All_Amount,
16                G.Goods_ID,
17                G.Goods_Name,
18                G.Goods_Unit,
19                ROW_NUMBER() over(order by Stock_Amount desc) as rowid
20           from T_IM_StockInfo as S
21          inner join T_Base_GoodsInfo AS G
22             on S.Goods_ID = G.Goods_ID
23          inner join temp
24             on temp.Type_ID = G.Goods_TypeID
25          where S.Shop_ID = @shop_id
26            AND G.Shop_ID = @shop_id
27            and G.Goods_TypeID = temp.Type_ID
28          group by S.Stock_Amount,
29                   S.All_Amount,
30                   G.Goods_ID,
31                   G.Goods_Name,
32                   G.Goods_Unit,
33                   S.StockWarn_Amount
34         HAVING SUM(S.Stock_Amount) < S.StockWarn_Amount) m
35  WHERE rowid between @pageindex and @pagesize

View Code

参考：sql with as 用法