gpt4 book ai didi

sql - 如何重用公共(public)表表达式

转载 作者:行者123 更新时间:2023-12-01 20:18:49 24 4
gpt4 key购买 nike

我正在使用通用表表达式进行分页:

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

在进行此查询后,我立即进行几乎相同的查询以检索项目总数:

with query as (
Select Row_Number() over (Order By OrderNum ASC) as TableRowNum,
FirstName,
LastName
From Users
)
Select Count(*) from query

我尝试将它们组合在一起(即:定义 CTE,查询数据,然后查询计数,但是当我这样做时,我收到一条错误消息“无效的对象名称'查询'”来响应第二个查询(伯爵)。

有没有办法将这两个查询合并为一个,以节省与数据库的往返?

最佳答案

如果您在两个不同的查询中不需要它们,您可以尝试

;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

如果您确实需要 2 个不同的查询,请使用表变量

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

关于sql - 如何重用公共(public)表表达式,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/1801976/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com