我已经开始阅读关于公共表表达式的文章,但我想不出有什么用例需要使用它们。它们似乎是多余的,因为派生表也可以这样做。是我忽略了什么还是没有理解好?谁能给我一个简单的例子,限制与常规选择,衍生或临时表查询,使CTE的情况?任何简单的例子将高度赞赏。
当前回答
还有一点没有指出,就是速度。我知道这是一个老问题,但我认为这值得直接评论/回答:
它们似乎是多余的,因为派生表也可以这样做
当我第一次使用CTE时,我完全被它的速度惊呆了。这是教科书上的案例,非常适合CTE,但在我使用CTE的所有情况下,都有显著的速度增益。我的第一个查询很复杂,涉及派生表,执行时间很长。对于CTE,它只需要几秒钟,让我震惊,这甚至是可能的。
其他回答
还有一点没有指出,就是速度。我知道这是一个老问题,但我认为这值得直接评论/回答:
它们似乎是多余的,因为派生表也可以这样做
当我第一次使用CTE时,我完全被它的速度惊呆了。这是教科书上的案例,非常适合CTE,但在我使用CTE的所有情况下,都有显著的速度增益。我的第一个查询很复杂,涉及派生表,执行时间很长。对于CTE,它只需要几秒钟,让我震惊,这甚至是可能的。
我认为使用cte有两个原因。
在where子句中使用计算值。对我来说,这似乎比派生表更清晰一些。
假设有两个表-问题和答案由问题连接在一起。ID =答案。Question_Id(和测验id)
WITH CTE AS
(
Select Question_Text,
(SELECT Count(*) FROM Answers A WHERE A.Question_ID = Q.ID) AS Number_Of_Answers
FROM Questions Q
)
SELECT * FROM CTE
WHERE Number_Of_Answers > 0
这是另一个例子,我想要得到一个问题和答案列表。我希望答案与结果中的问题组合在一起。
WITH cte AS
(
SELECT [Quiz_ID]
,[ID] AS Question_Id
,null AS Answer_Id
,[Question_Text]
,null AS Answer
,1 AS Is_Question
FROM [Questions]
UNION ALL
SELECT Q.[Quiz_ID]
,[Question_ID]
,A.[ID] AS Answer_Id
,Q.Question_Text
,[Answer]
,0 AS Is_Question
FROM [Answers] A INNER JOIN [Questions] Q ON Q.Quiz_ID = A.Quiz_ID AND Q.Id = A.Question_Id
)
SELECT
Quiz_Id,
Question_Id,
Is_Question,
(CASE WHEN Answer IS NULL THEN Question_Text ELSE Answer END) as Name
FROM cte
GROUP BY Quiz_Id, Question_Id, Answer_id, Question_Text, Answer, Is_Question
order by Quiz_Id, Question_Id, Is_Question Desc, Name
;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的递归查询
激动人心的微软定义 摘自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;
享受
推荐文章
- 如何在Ruby On Rails中使用NuoDB手动执行SQL命令
- 查询JSON类型内的数组元素
- 确定记录是否存在的最快方法
- 获得PostgreSQL数据库中当前连接数的正确查询
- 在SQL选择语句Order By 1的目的是什么?
- 从现有模式生成表关系图(SQL Server)
- 我如何循环通过一组记录在SQL Server?
- 数据库和模式的区别
- 如何在SQL Server中一次更改多个列
- 如何从命令行通过mysql运行一个查询?
- 外键约束可能导致循环或多条级联路径?
- 使用LIMIT/OFFSET运行查询,还可以获得总行数
- 当恢复sql时,psql无效命令\N
- 货币应该使用哪种数据类型?
- 如何选择每一行的列值不是独特的