保持简单以及如何在查询中执行多个CTE [英] Keeping it simple and how to do multiple CTE in a query

问题描述

我有一个简单的T-SQL查询，它从一个表中发出一堆列，并且还联接来自其他相关表中的信息。

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

我的解决方案是添加一个CTE，该CTE对已安排的事件进行分组并计算参与者的数量。

这将允许我在每个计划的活动中加入该信息。保持查询简单。

但是，我希望保持查询简单，如果将来我需要在简单查询期间访问其他临时结果，该怎么办？是吗？

如果可以有多个CTE，但我不能，我真的很喜欢，对吧？我在这里有什么选择？

我已经排除了视图和在应用程序数据层中所做的事情。我更喜欢隔离我的SQL查询。

解决方案

您可以有多个 CTE 在一个查询中，以及重用 CTE ：

 使用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 
  

但是请注意， SQL Server 可能会重新评估 CTE 每次访问，因此，如果您使用的是 RAND（）， NEWID（）等，它们可能会在 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.

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.

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?

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

解决方案

You can have multiple CTEs in one query, as well as reuse a 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.

这篇关于保持简单以及如何在查询中执行多个CTE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！