如何重用通用表表达式 [英] How can I reuse a Common Table Expression
问题描述
我使用通用表表达式进行分页:
,查询为(
选择Row_Number() (按OrderNum ASC排序)为TableRowNum,
FirstName,
姓氏
来自用户
)
从查询中选择*其中TableRowNum在1到25之间按TableRowNum ASC b $ b
在进行此查询后,立即进行几乎相同的查询以检索项目总数:
,查询为(
选择Row_Number()而不是(Order By OrderNum ASC)作为TableRowNum,
名字,
姓氏
来自用户
)
从查询
$ b中选择Count(*)
$ b
我尝试将它们组合在一起(即:定义CTE,查询数据,然后查询Count,但是当我这样做时,我收到一条错误消息无效的对象名称'query'响应第二个查询(计数)。
有什么方法可以将这两个查询合并为一个,以保存到数据库的往返行程?
如果在2中不需要它们不同的查询,您可以尝试
;查询为(
选择Row_Number()以上(按用户ID ASC排序) as TableRowNum,
FirstName,
LastName
来自用户
),
totalCount AS(
SELECT COUNT(1)来自查询
的总计)
选择查询。*,
来自查询的总计
,totalCount
,其中TableRowNum
在1到25之间
通过TableRowNum ASC
排序
如果确实需要2个不同的查询,则使用表var
DECLARE @User TABLE(
中选择COUNT(1) / pre>
TableRowNum INT,
FirstName VARCHAR(50),
LastName VARCHAR(50)
)
;查询为(
选择Row_Number()作为(Table by UserID ASC)作为TableRowNum,
FirstName,
LastName
来自用户
)
插入@User
SELECT TableRowNum,
FirstName,
LastName
FROM查询
SELECT *
FROM @User
,其中TableRowNum
在1到25之间
由TableRowNum排序ASC
从@User
I am using a Common Table Expression for paging:
with query as ( Select Row_Number() over (Order By OrderNum ASC) as TableRowNum, FirstName, LastName From Users ) Select * from query where TableRowNum between 1 and 25 Order By TableRowNum ASC
Immediately after making this query, I make make an almost identical query in order to retrieve the total number of items:
with query as ( Select Row_Number() over (Order By OrderNum ASC) as TableRowNum, FirstName, LastName From Users ) Select Count(*) from query
I have tried combining these together (ie: define the CTE, query the data and then query the Count, but when I do this, I get an error message "Invalid object name 'query'" in response the the second query (the Count).
Is there any way to combine these two queries into one, to save a round-trip to the DB?
解决方案If you do not require them in 2 different queries, you can try
;with query as ( Select Row_Number() over (Order By UserID ASC) as TableRowNum, FirstName, LastName From Users ), totalCount AS ( SELECT COUNT(1) Total FROM query ) Select query.*, Total from query, totalCount where TableRowNum between 1 and 25 Order By TableRowNum ASC
If you do require 2 different queries, rather use a table var
DECLARE @User TABLE( TableRowNum INT, FirstName VARCHAR(50), LastName VARCHAR(50) ) ;with query as ( Select Row_Number() over (Order By UserID ASC) as TableRowNum, FirstName, LastName From Users ) INSERT INTO @User SELECT TableRowNum, FirstName, LastName FROM query SELECT * FROM @User where TableRowNum between 1 and 25 Order By TableRowNum ASC SELECT COUNT(1) FROM @User
这篇关于如何重用通用表表达式的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!