我已经开始阅读关于公共表表达式的文章,但我想不出有什么用例需要使用它们。它们似乎是多余的,因为派生表也可以这样做。是我忽略了什么还是没有理解好?谁能给我一个简单的例子,限制与常规选择,衍生或临时表查询,使CTE的情况?任何简单的例子将高度赞赏。
当前回答
One of the scenarios I found useful to use CTE is when you want to get DISTINCT rows of data based on one or more columns but return all columns in the table. With a standard query you might first have to dump the distinct values into a temp table and then try to join them back to the original table to retrieve the rest of the columns or you might write an extremely complex partition query that can return the results in one run but in most likelihood, it will be unreadable and cause performance issue.
但是通过使用CTE(由Tim Schmelter在选择记录的第一个实例中回答)
WITH CTE AS(
SELECT myTable.*
, RN = ROW_NUMBER()OVER(PARTITION BY patientID ORDER BY ID)
FROM myTable
)
SELECT * FROM CTE
WHERE RN = 1
如您所见,这更容易阅读和维护。与其他查询相比,它的性能要好得多。
其他回答
举个例子,如果需要多次引用/加入相同的数据集,可以通过定义CTE来实现。因此,它可以是代码重用的一种形式。
自引用的一个例子是递归:使用CTE的递归查询
激动人心的微软定义 摘自Books Online:
CTE可用于:
创建递归查询。有关更多信息,请参见使用公共表表达式的递归查询。 当不需要一般使用视图时,替换为视图;也就是说,您不必将定义存储在元数据中。 根据从标量子选择派生的列或非确定性或具有外部访问权限的函数启用分组。 在同一语句中多次引用结果表。
也许将CTE视为用于单个查询的视图的替代品更有意义。但不需要开销、元数据或正式视图的持久性。非常有用,当你需要:
创建递归查询。 在查询中多次使用CTE的结果集。 通过减少大量相同的子查询来提高查询的清晰度。 根据CTE结果集中派生的列启用分组
下面是一个剪切粘贴的例子:
WITH [cte_example] AS (
SELECT 1 AS [myNum], 'a num' as [label]
UNION ALL
SELECT [myNum]+1,[label]
FROM [cte_example]
WHERE [myNum] <= 10
)
SELECT * FROM [cte_example]
UNION
SELECT SUM([myNum]), 'sum_all' FROM [cte_example]
UNION
SELECT SUM([myNum]), 'sum_odd' FROM [cte_example] WHERE [myNum] % 2 = 1
UNION
SELECT SUM([myNum]), 'sum_even' FROM [cte_example] WHERE [myNum] % 2 = 0;
享受
还有一点没有指出,就是速度。我知道这是一个老问题,但我认为这值得直接评论/回答:
它们似乎是多余的,因为派生表也可以这样做
当我第一次使用CTE时,我完全被它的速度惊呆了。这是教科书上的案例,非常适合CTE,但在我使用CTE的所有情况下,都有显著的速度增益。我的第一个查询很复杂,涉及派生表,执行时间很长。对于CTE,它只需要几秒钟,让我震惊,这甚至是可能的。
One of the scenarios I found useful to use CTE is when you want to get DISTINCT rows of data based on one or more columns but return all columns in the table. With a standard query you might first have to dump the distinct values into a temp table and then try to join them back to the original table to retrieve the rest of the columns or you might write an extremely complex partition query that can return the results in one run but in most likelihood, it will be unreadable and cause performance issue.
但是通过使用CTE(由Tim Schmelter在选择记录的第一个实例中回答)
WITH CTE AS(
SELECT myTable.*
, RN = ROW_NUMBER()OVER(PARTITION BY patientID ORDER BY ID)
FROM myTable
)
SELECT * FROM CTE
WHERE RN = 1
如您所见,这更容易阅读和维护。与其他查询相比,它的性能要好得多。
;with cte as
(
Select Department, Max(salary) as MaxSalary
from test
group by department
)
select t.* from test t join cte c on c.department=t.department
where t.salary=c.MaxSalary;
试试这个
推荐文章
- 在SQL Server上使用varchar(MAX) vs TEXT
- Visual Studio: ContextSwitchDeadlock
- Sql Server字符串到日期的转换
- 将一列的多个结果行连接为一列,按另一列分组
- 检查MySQL表是否存在而不使用“select from”语法?
- 如何将SQL Azure数据库复制到本地开发服务器?
- SQL Server 2008不能用新创建的用户登录
- 在PostgreSQL中快速发现表的行数
- 更改varchar列的大小为较低的长度
- 从表中选择1是什么意思?
- SQL Server中User和Login的区别
- 在参数声明中,varchar(MAX)的大小是多少?
- 数据库性能调优有哪些资源?
- 如何更改表的默认排序规则?
- 为两列的组合添加唯一的约束