Cte可以使用两次吗? [英] Can Cte be used twice ?
本文介绍了Cte可以使用两次吗?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
- 场景
ALTER PROCEDURE [dbo]。[APPROVED_KM] - 20,0,15,4
(
@ HUB_ID INT ,
< span class =code-sdkkeyword> @ Pageindex int ,
@ pageSize int ,
@ totalrows int 输出
)
AS
BEGIN
< pre lang =cs>; cte as
(
- 所有代码都写在cte中,我没有在这里显示
)
- cte后的第一次查询
选择
@ totalrows = count(rownum)
来自 cte
- cte之后的第二次查询
选择
*
来自
cte
订单 by address_id
OFFSET((@ pageindex + 1)-1)* @ pageSize ROWS
FETCH NEXT @ pageSize 仅限行
---现在当我执行存储过程时....无效对象cte
---------------------------- ------为了避免这个问题我试过这个-----------------------
选择 *
进入#temp1
来自 cte
选择
@totalrows = count(rownum)
来自
#temp1
print @ totalrows
选择
*
来自
#temp1
订单 < span class =code-keyword> by address_id
OFFSET((@ pageindex + 1)-1)* @ pageSize ROWS
FETCH NEXT @ pageSize 仅限行
- 有什么方法可以不插入临时表...是否可以用cte本身[执行两个查询]
解决方案
不,你可以' t在CTE之外使用CTE。唯一的方法是使用临时表;)
; WITH CTE AS
(
- SELECT ...
)
INSERT INTO @tmp (DestField1,DestField2,...)
SELECT (CteField1,CteField2,...)
FROM CTE
SELECT @ total = COUNT(*)
FROM @ tmp
- 依此类推......
>
--scenario
ALTER PROCEDURE [dbo].[APPROVED_KM]-- 20,0,15,4
(
@HUB_ID INT,
@Pageindex int,
@pageSize int,
@totalrows int output
)
AS
BEGIN
; with cte as
(
--all the codes are written inside the cte which i am not showing here
)
--first query after cte
select
@totalrows=count(rownum)
from cte
--second query after cte
select
*
from
cte
order by address_id
OFFSET ((@Pageindex+1)-1)*@pageSize ROWS
FETCH NEXT @pageSize ROWS ONLY
---now when i executed the stored procedure ...."invalid object cte"
----------------------------------to avoid the problem i tried this-----------------------
select *
into #temp1
from cte
select
@totalrows=count(rownum)
from
#temp1
print @totalrows
select
*
from
#temp1
order by address_id
OFFSET ((@Pageindex+1)-1)*@pageSize ROWS
FETCH NEXT @pageSize ROWS ONLY
--is there any way to do without inserting into temporary table...is it possible to do with cte itself [execute the two query]
解决方案
No, you can't use CTE outside the CTE. The only way is to use temporary table ;)
;WITH CTE AS ( --SELECT ... ) INSERT INTO @tmp (DestField1, DestField2, ...) SELECT (CteField1, CteField2, ...) FROM CTE SELECT @total = COUNT(*) FROM @tmp --and so on...
这篇关于Cte可以使用两次吗?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文