如何两次引用一个CTE? [英] How to reference one CTE twice?
本文介绍了如何两次引用一个CTE?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个非常胖的公用表表达式,其中包含行号,以便可以返回分页的结果集。在分页结果集之前,我还想返回与查询匹配的记录总数。
I have a very fat common table expression which includes row numbers so that I can return a paged result set. I also want to return the total number of records that match the query before I page the result set.
with recs as (select *, row_number() over (order by id) as rownum from ......)
select * from recs where rownum between @a and @b .... select count(*) from recs
很显然,我上面的查询是不完整的,但这只是为了说明我的观点。我想要一个结果页面以及匹配总数。如何做到这一点而又不必照搬整个C +行的整个20粘贴?
Obviously my query above is patchy, but it's just for illustrating my point. I want a page of results AND the total number of matches. How do I do this without having to literally copy and paste the entire 20+ line CTE?
推荐答案
您可以使用逗号创建
仅说明我的意思:
with recs as (
select
*,
row_number() over (order by id) as rownum from ......
),
counts as (
select count(*) as totalrows from recs
)
select recs.*,count.totalrows
from recs
cross apply counts
where rownum between @a and @b ....
这不是一个好的解决方案。
This is not the a good solution.
DECLARE @startRow INT; SET @startrow = 50;
WITH cols
AS
(
SELECT table_name, column_name,
ROW_NUMBER() OVER(ORDER BY table_name, column_name) AS seq,
ROW_NUMBER() OVER(ORDER BY table_name DESC, column_name desc) AS totrows
FROM [INFORMATION_SCHEMA].columns
)
SELECT table_name, column_name, totrows + seq -1 as TotRows
FROM cols
WHERE seq BETWEEN @startRow AND @startRow + 49
ORDERBY seq
这篇关于如何两次引用一个CTE?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文