我们像下面这样编写CTE SQL:
WITH yourCTE AS
(
SELECT .... FROM :... WHERE.....
) SELECT * FROM yourCTE
在with块中放置SQL语句有什么优势?我认为,如果我们将复杂的SQL放在with块中,那么我们就可以像访问视图一样编写SQL,如SELECT * FROM yourCTE
。
请讨论使用公共表表达式(CTE)在性能方面的附加优势。谢谢。
我们像下面这样编写CTE SQL:
WITH yourCTE AS
(
SELECT .... FROM :... WHERE.....
) SELECT * FROM yourCTE
在with块中放置SQL语句有什么优势?我认为,如果我们将复杂的SQL放在with块中,那么我们就可以像访问视图一样编写SQL,如SELECT * FROM yourCTE
。
请讨论使用公共表表达式(CTE)在性能方面的附加优势。谢谢。
有许多情况下可以使用CTE:
递归查询,例如遍历层次树 - 如果没有CTE,则非常棘手和麻烦(请参见此处的递归CTE示例)
任何时候都想使用排名函数,如 ROW_NUMBER()
, RANK()
, NTILE()
等 (请参见此处关于排名函数的信息)
通常情况下,需要先基于某些条件选择几行/列,然后对这些行/列执行某些操作,例如更新表、删除重复项等。
我经常使用CTE来删除给定数据集中除最近一行之外的所有行。例如,如果您有客户和与其订单相关的1:n关系,并且要删除每个客户的除最新订单以外的所有订单(基于OrderDate
),则在SQL中执行此操作会变得相当麻烦。
有了CTE和排名函数,就很容易了:
;WITH CustomerOrders AS
(
SELECT
c.CustomerID, o.OrderID,
ROW_NUMBER() OVER(PARTITION BY c.CustomerID ORDER BY o.OrderDate DESC) AS 'RowN'
FROM
dbo.Customer c
INNER JOIN
dbo.Orders o ON o.CustomerID = c.CustomerID
)
DELETE FROM
dbo.Orders
FROM
CustomerOrders co
WHERE
dbo.Orders.OrderID = co.OrderID
AND co.RowN > 1
使用这种方法,你创建了一个“内联视图”,并按照CustomerID
进行分区(例如,每个客户都会得到以1开始的行号),按照OrderDate DESC
(最新订单在前)排序。对于每个客户,最新的、最近的订单具有RowN = 1
,因此您可以轻松地删除所有其他行,这就完成了您想要的操作——使用CTE非常简单——没有它就变得混乱的代码....
另一件事是,如果您需要多次查询相同的联接数据子集,并且没有为其定义视图,则CTE可能会有益处。如果您只是为单个查询连接数据,然后将其包装在CTE中,那么我认为这是过度的。即使您没有使用CTE,查询路径仍将被缓存...
晚上好朋友们。今天我们将学习关于常用表达式的内容,这是SQL Server 2005中引入的新功能,并在后续版本中提供。
常用表达式:常用表达式可以被定义为临时结果集,或者换句话说,它是SQL Server中视图的替代品。常用表达式仅在定义它的语句批处理中有效,不能在其他会话中使用。
声明CTE(常用表达式)的语法:
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执行删除和更新语句,并且这将直接影响在CTE中使用的引用表。让我们通过一个例子来理解这个事实。
With CTE_Example(EID,DeptName)
as
(
select EID,DeptName from DEPT
)
delete from CTE_Example where EID=10 and DeptName ='Payroll'