问题描述
我有一个简单的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 CTE
s 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的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!