我已经开始阅读关于公共表表达式的文章,但我想不出有什么用例需要使用它们。它们似乎是多余的,因为派生表也可以这样做。是我忽略了什么还是没有理解好?谁能给我一个简单的例子,限制与常规选择,衍生或临时表查询,使CTE的情况?任何简单的例子将高度赞赏。
当前回答
举个例子,如果需要多次引用/加入相同的数据集,可以通过定义CTE来实现。因此,它可以是代码重用的一种形式。
自引用的一个例子是递归:使用CTE的递归查询
激动人心的微软定义 摘自Books Online:
CTE可用于:
创建递归查询。有关更多信息,请参见使用公共表表达式的递归查询。 当不需要一般使用视图时,替换为视图;也就是说,您不必将定义存储在元数据中。 根据从标量子选择派生的列或非确定性或具有外部访问权限的函数启用分组。 在同一语句中多次引用结果表。
其他回答
还有一点没有指出,就是速度。我知道这是一个老问题,但我认为这值得直接评论/回答:
它们似乎是多余的,因为派生表也可以这样做
当我第一次使用CTE时,我完全被它的速度惊呆了。这是教科书上的案例,非常适合CTE,但在我使用CTE的所有情况下,都有显著的速度增益。我的第一个查询很复杂,涉及派生表,执行时间很长。对于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有两个原因。
在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
当您希望执行“有序更新”时,它非常有用。
MS SQL不允许你使用ORDER BY UPDATE,但在CTE的帮助下,你可以这样做:
WITH cte AS
(
SELECT TOP(5000) message_compressed, message, exception_compressed, exception
FROM logs
WHERE Id >= 5519694
ORDER BY Id
)
UPDATE cte
SET message_compressed = COMPRESS(message), exception_compressed = COMPRESS(exception)
看这里更多的信息:如何更新和使用ms sql
我使用它们来分解复杂的查询,特别是复杂的连接和子查询。我发现我越来越多地使用它们作为“伪视图”来帮助我理解查询的意图。
我唯一抱怨的是它们不能重复使用。例如,我可能有一个带有两个更新语句的存储过程,它们可以使用相同的CTE。但是CTE的“范围”只是第一个查询。
问题是,“简单的例子”可能真的不需要CTE !
不过,还是很方便。
推荐文章
- GROUP BY with MAX(DATE)
- 删除id与其他表不匹配的sql行
- 等价的限制和偏移SQL Server?
- 如何从SQL Server中的字符串中剥离所有非字母字符?
- 为什么我不能在DELETE语句中使用别名?
- 在SQL Server Management Studio中保存带有标题的结果
- "where 1=1"语句
- 如何选择一个记录和更新它,与一个单一的查询集在Django?
- 多语句表值函数vs内联表值函数
- 如何从Oracle的表中获取列名?
- NOLOCK提示在SELECT语句中的作用
- SQL OVER()子句-它什么时候有用,为什么有用?
- 如果字段在MySQL中为空,则返回0
- 检查SQL Server登录是否已经存在
- 我如何使用ROW_NUMBER()?