我已经开始阅读关于公共表表达式的文章,但我想不出有什么用例需要使用它们。它们似乎是多余的,因为派生表也可以这样做。是我忽略了什么还是没有理解好?谁能给我一个简单的例子,限制与常规选择,衍生或临时表查询,使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视为用于单个查询的视图的替代品更有意义。但不需要开销、元数据或正式视图的持久性。非常有用,当你需要:
创建递归查询。 在查询中多次使用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的递归查询
激动人心的微软定义 摘自Books Online:
CTE可用于:
创建递归查询。有关更多信息,请参见使用公共表表达式的递归查询。 当不需要一般使用视图时,替换为视图;也就是说,您不必将定义存储在元数据中。 根据从标量子选择派生的列或非确定性或具有外部访问权限的函数启用分组。 在同一语句中多次引用结果表。
;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;
试试这个
还有一点没有指出,就是速度。我知道这是一个老问题,但我认为这值得直接评论/回答:
它们似乎是多余的,因为派生表也可以这样做
当我第一次使用CTE时,我完全被它的速度惊呆了。这是教科书上的案例,非常适合CTE,但在我使用CTE的所有情况下,都有显著的速度增益。我的第一个查询很复杂,涉及派生表,执行时间很长。对于CTE,它只需要几秒钟,让我震惊,这甚至是可能的。
推荐文章
- 如何在Ruby On Rails中使用NuoDB手动执行SQL命令
- 查询JSON类型内的数组元素
- 确定记录是否存在的最快方法
- 获得PostgreSQL数据库中当前连接数的正确查询
- 在SQL选择语句Order By 1的目的是什么?
- 从现有模式生成表关系图(SQL Server)
- 我如何循环通过一组记录在SQL Server?
- 数据库和模式的区别
- 如何在SQL Server中一次更改多个列
- 如何从命令行通过mysql运行一个查询?
- 外键约束可能导致循环或多条级联路径?
- 使用LIMIT/OFFSET运行查询,还可以获得总行数
- 当恢复sql时,psql无效命令\N
- 货币应该使用哪种数据类型?
- 如何选择每一行的列值不是独特的