WITH AS短语,也叫做子查询部分(subquery factoring)
可以让你做很多事情,定义一个SQL片断,该SQL片断会被整个SQL语句所用到。
作为提供数据的部分。
代码例子:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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