客服电话
APP下载
迪恩网络APP
随时随地掌握行业动态
官方微信
扫描二维码
关注迪恩网络微信公众号
|
一．WITH AS的含义 WITH AS短语，也叫做子查询部分（subquery factoring），可以让你做很多事情，定义一个SQL片断，该SQL片断会被整个SQL语句所用到。有的时候，是为了让SQL语句的可读性更高些，也有可能是在UNION ALL的不同部分，作为提供数据的部分。 二．使用方法 先看下面一个嵌套的查询语句：
上面的查询语句使用了一个子查询。虽然这条SQL语句并不复杂，但如果嵌套的层次过多，会使SQL语句非常难以阅读和维护。因此，也可以使用表变量的方式来解决这个问题，SQL语句如下：
虽然上面的SQL语句要比第一种方式更复杂，但却将子查询放在了表变量@t中，这样做将使SQL语句更容易维护，但又会带来另一个问题，就是性能的损失。由于表变量实际上使用了临时表，从而增加了额外的I/O开销，因此，表变量的方式并不太适合数据量大且频繁查询的情况。为此，在SQL Server 2005中提供了另外一种解决方案，这就是公用表表达式（CTE），使用CTE，可以使SQL语句的可维护性，同时，CTE要比表变量的效率高得多。 下面是CTE的语法：
现在使用CTE来解决上面的问题，SQL语句如下：
其中cr是一个公用表表达式，该表达式在使用上与表变量类似，只是SQL Server 2005在处理公用表表达式的方式上有所不同。 在使用CTE时应注意如下几点： 1. CTE后面必须直接跟使用CTE的SQL语句（如select、insert、update等），否则，CTE将失效。如下面的SQL语句将无法正常使用CTE： with cr as ( select CountryRegionCode from person.CountryRegion where Name like 'C%' ) select * from person.CountryRegion -- 应将这条SQL语句去掉 -- 使用CTE的SQL语句应紧跟在相关的CTE后面 -- select * from person.StateProvince where CountryRegionCode in (select * from cr) 2. CTE后面也可以跟其他的CTE，但只能使用一个with，多个CTE中间用逗号（,）分隔，如下面的SQL语句所示： 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 3. 如果CTE的表达式名称与某个数据表或视图重名，则紧跟在该CTE后面的SQL语句使用的仍然是CTE，当然，后面的SQL语句使用的就是数据表或视图了，如下面的SQL语句所示：
4. CTE 可以引用自身，也可以引用在同一 WITH 子句中预先定义的 CTE。不允许前向引用。 5. 不能在 CTE_query_definition 中使用以下子句： （1）COMPUTE 或 COMPUTE BY （2）ORDER BY（除非指定了 TOP 子句） （3）INTO （4）带有查询提示的 OPTION 子句 （5）FOR XML （6）FOR BROWSE 6. 如果将 CTE 用在属于批处理的一部分的语句中，那么在它之前的语句必须以分号结尾，如下面的SQL所示： declare @s nvarchar(3) set @s = 'C%' ; -- 必须加分号 with t_tree as ( select CountryRegionCode from person.CountryRegion where Name like @s ) select * from person.StateProvince where CountryRegionCode in (select * from t_tree) CTE除了可以简化嵌套SQL语句外，还可以进行递归调用，关于这一部分的内容将在下一篇文章中介绍。 先看如下一个数据表（t_tree）： 上图显示了一个表中的数据，这个表有三个字段：id、node_name、parent_id。实际上，这个表中保存了一个树型结构，分三层：省、市、区。其中id表示当前省、市或区的id号、node_name表示名称、parent_id表示节点的父节点的id。 从这个需求来看属于递归调用，也就是说先查出满足调价的省的记录，在本例子中的要查“辽宁省”的记录，如下： id node_name parent_id 1 辽宁省 0 然后再查所有parent_id字段值为1的记录，如下： id node_name parent_id 2 沈阳市 1 3 大连市 1 最后再查parent_id字段值为2或3的记录，如下： id node_name parent_id 4 大东区 2 5 沈河区 2 6 铁西区 2 将上面三个结果集合并起来就是最终结果集。 上述的查询过程也可以按递归的过程进行理解，即先查指定的省的记录（辽宁省），得到这条记录后，就有了相应的id值，然后就进入了的递归过程，如下图所示。 从上面可以看出，递归的过程就是使用union all合并查询结果集的过程，也就是相当于下面的递归公式： resultset(n) = resultset(n-1) union all current_resultset 其中resultset(n)表示最终的结果集，resultset(n - 1)表示倒数第二个结果集，current_resultset表示当前查出来的结果集，而最开始查询出“辽宁省”的记录集相当于递归的初始条件。而递归的结束条件是current_resultset为空。下面是这个递归过程的伪代码：
public resultset getResultSet(resultset)
{
if(resultset is null)
{
current_resultset =第一个结果集（包含省的记录集）
将结果集的id保存在集合中
getResultSet(current_resultset)
}
current_resultset = 根据id集合中的id值查出当前结果集
if(current_result is null) return resultset
将当前结果集的id保存在集合中
return getResultSet(resultset union all current_resultset)
}
// 获得最终结果集
resultset = getResultSet(null)
从上面的过程可以看出，这一递归过程实现起来比较复杂，然而CTE为我们提供了简单的语法来简化这一过程。
sql语句
with
district as
(
-- 获得第一个结果集，并更新最终结果集
select * from t_tree where node_name= N'辽宁省'
union all
-- 下面的select语句首先会根据从上一个查询结果集中获得的id值来查询parent_id
-- 字段的值，然后district就会变当前的查询结果集，并继续执行下面的select 语句
-- 如果结果集不为null，则与最终的查询结果合并，同时用合并的结果更新最终的查
-- 询结果；否则停止执行。最后district的结果集就是最终结果集。
select a.* from t_tree a, district b
where a.parent_id = b.id
)
select * from district
with
district as
(
select * from t_tree where node_name= N'辽宁省'
union all
select a.* from t_tree a, district b
where a.parent_id = b.id
),
district1 as
(
select a.* from district a where a.id in (select parent_id from district)
)
select * from district1
注：只有“辽宁省”和“沈阳市”有下子节点。 在定义和使用递归CTE时应注意如下几点： 1. 递归 CTE 定义至少必须包含两个 CTE 查询定义，一个定位点成员和一个递归成员。可以定义多个定位点成员和递归成员；但必须将所有定位点成员查询定义置于第一个递归成员定义之前。所有 CTE 查询定义都是定位点成员，但它们引用 CTE 本身时除外。 （1）SELECT DISTINCT 7. 无论参与的 SELECT 语句返回的列的为空性如何，递归 CTE 返回的全部列都可以为空。 下面是一些补充，很多参考价值 WITH AS短语，也叫做子查询部分（subquery factoring） 作为提供数据的部分。 代码例子：
with temp as
(select ID, Type_Name, Type_ID
from T_Base_GoodsType as t
where t.Shop_ID = @shop_id
and Type_ID = @Goods_TypeID
union all
select t1.ID, t1.Type_Name, t1.Type_ID
from T_Base_GoodsType as t1
inner join temp
on t1.ParentType_ID = temp.Type_ID
where t1.Shop_ID = @shop_id)
select *
from (select Stock_Amount,
S.StockWarn_Amount,
S.All_Amount,
G.Goods_ID,
G.Goods_Name,
G.Goods_Unit,
ROW_NUMBER() over(order by Stock_Amount desc) as rowid
from T_IM_StockInfo as S
inner join T_Base_GoodsInfo AS G
on S.Goods_ID = G.Goods_ID
inner join temp
on temp.Type_ID = G.Goods_TypeID
where S.Shop_ID = @shop_id
AND G.Shop_ID = @shop_id
and G.Goods_TypeID = temp.Type_ID
group by S.Stock_Amount,
S.All_Amount,
G.Goods_ID,
G.Goods_Name,
G.Goods_Unit,
S.StockWarn_Amount
HAVING SUM(S.Stock_Amount) < S.StockWarn_Amount) m
WHERE rowid between @pageindex and @pagesize
sql循环（WITH AS短语也叫做子查询部分） --表结构 SELECT id,position,Parentid FROM op_client_sales_structure WITH TEST_CTE AS ( SELECT id,position,Parentid,Cast(Parentid AS NVARCHAR(4000)) AS PATH FROM op_client_sales_structure team WHERE Parentid !=-1 UNION ALL SELECT a.id,a.position,a.Parentid, CTE.PATH+','+Cast(a.Parentid AS NVARCHAR(4000)) AS PATH FROM op_client_sales_structure a INNER JOIN TEST_CTE CTE ON a.id=CTE.Parentid ) SELECT * FROM TEST_CTE WHERE Parentid=(SELECT id FROM op_client_sales_structure WHERE Parentid=-1) --限制递归次数 OPTION(MAXRECURSION 10) 这篇文章就介绍到这，希望能帮助到你。 |
请发表评论