何时使用公共表表达式(CTE)

我已经开始阅读有关通用表表达式和不能想到一个用例，我需要使用他们。它们似乎是多余的，因为派生表也可以这样做。是我忽略了什么还是没有理解好?谁能给我一个简单的例子，限制与常规选择，衍生或临时表查询，使CTE的情况?任何简单的例子将高度赞赏。

383280 次浏览
小开

我使用它们来分解复杂的查询，特别是复杂的连接和子查询。我发现我越来越多地使用它们作为“伪视图”来帮助我理解查询的意图。

我唯一抱怨的是它们不能重复使用。例如，我可能有一个带有两个更新语句的存储过程，它们可以使用相同的CTE。但是CTE的“范围”只是第一个查询。

问题是，“简单的例子”可能真的不需要CTE !

不过，还是很方便。

小开
最佳答案

举个例子，如果需要多次引用/加入相同的数据集，可以通过定义CTE来实现。因此，它可以是代码重用的一种形式。

自引用的一个例子是递归:使用CTE的递归查询

激动人心的微软定义 摘自Books Online: < / p >

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
小开

今天我们将学习通用表表达式，这是SQL server 2005中引入的新特性，在后续版本中也可以使用。

公共表表达式:-公共表表达式可以定义为一个临时结果集，或者换句话说，它是SQL Server中视图的替代品。公共表表达式仅在定义它的语句批处理中有效，不能在其他会话中使用。

CTE(Common table expression)声明语法:-

with [Name of CTE]
as
(
Body of common table expression
)

举个例子:-

CREATE TABLE Employee([EID] [int] IDENTITY(10,5) NOT NULL,[Name] [varchar](50) NULL)


insert into Employee(Name) values('Neeraj')
insert into Employee(Name) values('dheeraj')
insert into Employee(Name) values('shayam')
insert into Employee(Name) values('vikas')
insert into Employee(Name) values('raj')


CREATE TABLE DEPT(EID INT,DEPTNAME VARCHAR(100))
insert into dept values(10,'IT')
insert into dept values(15,'Finance')
insert into dept values(20,'Admin')
insert into dept values(25,'HR')
insert into dept values(10,'Payroll')

我创建了两个表employee和Dept，并在每个表中插入5行。现在我想联接这些表并创建一个临时结果集以进一步使用它。

With CTE_Example(EID,Name,DeptName)
as
(
select Employee.EID,Name,DeptName from Employee
inner join DEPT on Employee.EID =DEPT.EID
)
select * from CTE_Example

让我们一行一行地理解这句话。

为了定义CTE，我们写了“with”子句，然后我们给表表达式起了一个名字，这里我给它起了一个名字“CTE_Example”

然后我们写“As”并将代码括在两个括号(——)中，我们可以在括号中连接多个表。

在最后一行中，我使用了“Select * from CTE_Example”，我们在最后一行代码中引用了公共表表达式，因此我们可以说它像一个视图，我们在单个批处理中定义和使用视图，CTE不存储在数据库中作为永久对象。但它的行为像一个视图。我们可以在CTE上执行delete和update语句，这将对CTE中使用的引用表产生直接影响。让我们举个例子来理解这个事实。

With CTE_Example(EID,DeptName)
as
(
select EID,DeptName from DEPT
)
delete from CTE_Example where EID=10 and DeptName ='Payroll'

在上面的语句中，我们从CTE_Example中删除一行，它将从CTE中使用的引用表“DEPT”中删除数据。

小开

我发现使用CTE很有用的一个场景是，当您希望根据一个或多个列获得DISTINCT行数据，但返回表中的所有列时。对于标准查询，您可能首先必须将不同的值转储到临时表中，然后尝试将它们连接回原始表以检索其余列，或者您可能编写一个极其复杂的分区查询，可以在一次运行中返回结果，但大多数情况下，它将不可读并导致性能问题。

但是通过使用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;

试试这个

小开

也许将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;

享受

小开

当您希望执行“有序更新”时，它非常有用。

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，它只需要几秒钟，让我震惊，这甚至是可能的。


提交答案