保持它的简单性以及如何在查询中执行多个CTE

I have this simple T-SQL query, it emits a bunch of columns from a table and also joins information from other related tables.

我有这个简单的T-SQL查询，它从表中发出一组列，并从其他相关表中连接信息。

My data model is simple. I have a scheduled event, with participants. I need to know how many participants participate in each event.

我的数据模型很简单。我有一个预定的活动，有参与者。我需要知道每个活动有多少参与者。

My solution to this is to add a CTE that groups scheduled events and counts the number of participants.

我的解决方案是添加一个CTE，它将计划的事件分组并计算参与者的数量。

This will allow me to join in that information per scheduled event. Keeping the query simple.

这将允许我加入每个计划事件的信息。保持简单的查询。

I like to keep my queries simple, however, If I ever in the future need to have additonal temporary results accessible during my simple query, what do I do?

我喜欢保持我的查询简单，但是，如果将来我需要在我的简单查询期间访问额外的临时结果，我该怎么办?

I would really like it, if I could have multiple CTEs but I can't, right? What are my options here?

我真的很喜欢它，如果我可以有多个cte但我不能，对吗?我的选择是什么?

I've ruled out views and doing things at the application data layer. I prefer to isolated my SQL queries.

我已经排除了视图，并在应用程序数据层进行操作。我更喜欢分离SQL查询。

2 个解决方案

#1

181

You can have multiple CTEs in one query, as well as reuse a CTE:

您可以在一个查询中有多个CTE，也可以重用CTE:

WITH    cte1 AS
        (
        SELECT  1 AS id
        ),
        cte2 AS
        (
        SELECT  2 AS id
        )
SELECT  *
FROM    cte1
UNION ALL
SELECT  *
FROM    cte2
UNION ALL
SELECT  *
FROM    cte1

Note, however, that SQL Server may reevaluate the CTE each time it is accessed, so if you are using values like RAND(), NEWID() etc., they may change between the CTE calls.

但是，请注意，SQL Server可能会在每次访问CTE时重新评估它，因此如果您使用RAND()、NEWID()等值，那么它们可能会在CTE调用之间发生变化。

#2

You certainly are able to have multiple CTE's in a single query expression. You just need to separate them with a comma. Here is an example. In the example below, there are two CTE's. One is named CategoryAndNumberOfProducts and the second is named ProductsOverTenDollars.

您当然可以在一个查询表达式中包含多个CTE。你只需要用逗号把它们分开。这是一个例子。在下面的示例中，有两个CTE。一个名为CategoryAndNumberOfProducts，另一个名为ProductsOverTenDollars。

WITH CategoryAndNumberOfProducts (CategoryID, CategoryName, NumberOfProducts) AS
(
   SELECT
      CategoryID,
      CategoryName,
      (SELECT COUNT(1) FROM Products p
       WHERE p.CategoryID = c.CategoryID) as NumberOfProducts
   FROM Categories c
),

ProductsOverTenDollars (ProductID, CategoryID, ProductName, UnitPrice) AS
(
   SELECT
      ProductID,
      CategoryID,
      ProductName,
      UnitPrice
   FROM Products p
   WHERE UnitPrice > 10.0
)

SELECT c.CategoryName, c.NumberOfProducts,
      p.ProductName, p.UnitPrice
FROM ProductsOverTenDollars p
   INNER JOIN CategoryAndNumberOfProducts c ON
      p.CategoryID = c.CategoryID
ORDER BY ProductName

#1

181