我已经开始阅读关于公共表表达式的文章,但我想不出有什么用例需要使用它们。它们似乎是多余的,因为派生表也可以这样做。是我忽略了什么还是没有理解好?谁能给我一个简单的例子,限制与常规选择,衍生或临时表查询,使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
其他回答
我使用它们来分解复杂的查询,特别是复杂的连接和子查询。我发现我越来越多地使用它们作为“伪视图”来帮助我理解查询的意图。
我唯一抱怨的是它们不能重复使用。例如,我可能有一个带有两个更新语句的存储过程,它们可以使用相同的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可用于:
创建递归查询。有关更多信息,请参见使用公共表表达式的递归查询。 当不需要一般使用视图时,替换为视图;也就是说,您不必将定义存储在元数据中。 根据从标量子选择派生的列或非确定性或具有外部访问权限的函数启用分组。 在同一语句中多次引用结果表。
当您希望执行“有序更新”时,它非常有用。
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的所有情况下,都有显著的速度增益。我的第一个查询很复杂,涉及派生表,执行时间很长。对于CTE,它只需要几秒钟,让我震惊,这甚至是可能的。
推荐文章
- 在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)的大小是多少?
- 数据库性能调优有哪些资源?
- 如何更改表的默认排序规则?
- 为两列的组合添加唯一的约束