How can I reuse a Common Table Expression
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 t开发者_运维百科he 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
You can do that like this :
with query as (
Select
COUNT (*) OVER (PARTITION BY 1) AS TableTotalRows,
Row_Number() over (Order By OrderNum ASC) as TableRowNum,
FirstName,
LastName
From Users
)
According to Microsoft in this link:
A CTE can reference itself and previously defined CTEs in the same WITH clause.
In that new CTE referencing the previous defined CTE, we can make the count query:
;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
'query' is the main CTE and 'totalCount' is using it for get the total rows count
Microsoft should have an example for a common task like this.
精彩评论