DECLARE @count INT;
WITH CTE AS
(SELECT...)
SELECT @count = COUNT(ID) FROM CTE;
SELECT * FROM CTE
我可以在第一个表达式SELECT @count = COUNT(ID) FROM CTE
后使用CTE表吗?我得到错误Invalid object name 'CTE'.
我用的是SQL Server 2008。
谢谢
你不能,真的;CTE只存在于单个语句的范围内。然而,在这种情况下,你可以这样做:
DECLARE @count INT;
WITH CTE AS (SELECT...)
SELECT * FROM CTE;
SELECT @count = @@ROWCOUNT;
如果您需要计数作为结果集的一部分,那么您只需输入:
WITH CTE AS (SELECT...)
SELECT *, COUNT(*) OVER() FROM CTE;
如因其他原因需要计数,请说明。
您可以使用表变量并在任何地方使用它。
DECLARE @count INT;
DECLARE @CTE AS TABLE (<<columns>>)
INSERT INTO @CTE (<<columns>>)
SELECT ....
WITH CTE AS
(SELECT * FROM @CTE)
SELECT @count = COUNT(ID) FROM CTE;
SELECT * FROM @CTE