SQL-CTE与VIEW [英] SQL - CTE vs VIEW

问题描述

我的问题是 SQL 中 CTE 与 View 有什么区别。我的意思是在那种情况下我应该使用 CTE ，在那种情况下我应该使用 View 。我知道它们都是某种虚拟表，但我无法区分它们的用途。

My question here is what is the difference between CTE and View in SQL. I mean in which case I should use the CTE and which case the View. I know that both are some kind of virtual tables but I can't differentiate their use.

我发现了一个类似的问题此处，但这与性能有关。

I found a similar question here but it's about performance.

更新1：

例如：我有一个充满交易的数据库（ tbl_trade ）。我需要从350万条记录中选择仅当月打开的交易，直到当前时间，然后处理数据（在虚拟表上使用不同的查询-这类似于查看）。这里的问题是我希望 SUM 包含3-4列，然后我需要 SUM 某些列和创建带有结果的虚拟列（看起来像CTE）。

For example: I have a database filled with trades(tbl_trade). I need to select from 3.5 millions records only the trades that was opened the current month until current time and then manipulate the data(with different queries on the virtual table - this looks like View). The problem here is that I want a SUM of 3-4 columns and then on I need to SUM some columns and create a virtual column with the result(looks like CTE).

例如： tbl_trade 具有列：利润，奖金和费用。
我需要 SUM（利润）， SUM（奖金）， SUM（费用）和新列总计等于 SUM（利润） + 总和（奖金） + 总和（费用）。

Eg: tbl_trade has columns: profit,bonus and expenses. I need SUM(profit),SUM(bonus),SUM(expenses) and a new column total which will be equal to SUM(profit)+SUM(bonus)+SUM(expenses).

PS。由于我已经有了结果，因此无法重新运行 SUM 的查询。

PS. Rerunning the queries for SUM is not an option since I already have the result.

请先谢谢！！

推荐答案

视图可以建立索引，但CTE不能。因此，这是重要的一点。

Views can be indexed but CTE can't. So this is one important point.

CTE在树层次结构 i上表现出色。递归

CTE work excellent on tree hierarchyi.e. recursive

另外，在处理复杂查询时也要考虑视图。视图是数据库上的物理对象（但不物理存储数据），可用于多个查询，因此提供了灵活性和集中式方法。另一方面，CTE是临时的，将在使用时创建。这就是为什么它们被称为内联视图。

Also, consider views when dealing with complex queries. Views being a physical object on database (but does not store data physically) and can be used on multiple queries, thus provide flexibility and centralized approach. CTE, on the other hand are temporary and will be created when they are used; that's why they are called as inline view.

更新

根据您更新的问题，视图将是正确的选择。在CTE中处理350万行将在TempDb上产生额外的开销，这最终会降低SQL Server的性能。请记住，CTE是一次性视图，因此不会存储任何统计信息，您也无法创建索引。就像子查询一样。

According to your updated question, views will be the right choice. Dealing with 3.5 million rows in CTE will create extra overhead on TempDb which will eventually slow down SQL Server performance. Remember, CTE is a disposable view hence no statistics are stored and you can't create Indexes too. It is just like a sub query.

这篇关于SQL-CTE与VIEW的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！