使用 SQL Server 2005 “WITH"的多选语句陈述 [英] Multiple Select Statements using SQL Server 2005 "WITH" Statement

问题描述

我正在尝试在 SQL Server 2005 中使用WITH"语句.如果我执行以下操作，它似乎可以正常工作:

WITH MyBigProducts AS (SELECT * FROM Products WHERE Size='Big')从 MyBigProducts 中选择名称

但是如果我尝试使用多个选择语句，它就会失败:

WITH MyBigProducts AS (SELECT * FROM Products WHERE Size='Big')从 MyBigProducts 中选择名称SELECT Count(*) FROM MyBigProducts

并且错误消息是无效的对象名称'MyBigProducts'".

我可以做些什么来增加MyBigProducts"表的范围以包含两个选择语句?

我的示例是对导致问题的实际代码的简化，因此我不排除上述简单示例应该可以工作并且我的 SQL 中存在另一个错误的可能性.>

我曾尝试将 BEGIN 和 END 包裹在两个 SELECT 语句周围，但解析器无法编译它.

解决方案

正如 Kane 所说，CTE 仅在编写它的 SQL 语句中可用.根据您的具体情况，另一种可能的解决方案是在单个查询中包含 COUNT(*):

;with MyBigProducts AS(选择姓名，COUNT(*) OVER() AS total_count从产品在哪里尺寸 = '大')选择姓名，总数从我的大产品

I am trying to use the "WITH" statement in SQL Server 2005. It seems to work fine if I do something like:

WITH MyBigProducts AS (SELECT * FROM Products WHERE Size='Big')
SELECT Name FROM MyBigProducts

But it fails if I try to use multiple select statements like:

WITH MyBigProducts AS (SELECT * FROM Products WHERE Size='Big')
SELECT Name FROM MyBigProducts
SELECT Count(*) FROM MyBigProducts

and the error message is "Invalid object name 'MyBigProducts'".

Is there something I can do to increase the scope of the "MyBigProducts" table to include both of the select statements?

My example is a simplification of my actual code causing the problem, so I'm not discounting the possibility that the above trivial example should work and that there is another bug in my SQL.

I have tried wrapping a BEGIN and END around the two SELECT statements, but the parser could not compile it.

解决方案

As Kane said, the CTE is only available in the SQL statement where it is written. Another possible solution, depending on the specifics of your situation, would be to include the COUNT(*) in the single query:

;WITH MyBigProducts AS
(
     SELECT
          Name,
          COUNT(*) OVER () AS total_count
     FROM
          Products
     WHERE
          Size = 'Big'
)
SELECT
     Name,
     total_count
FROM
     MyBigProducts

这篇关于使用 SQL Server 2005 “WITH"的多选语句陈述的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！