通用表表达式(Common Table Expression)

问题:编写由基本的 SELECT/FROM/WHERE 类型的语句派生而来的复杂 SQL 语句。

方案1:编写在From子句内使用派生表(内联视图)的T-SQL查询语句。

方案2:使用视图

方案3:使用通用表达式(CTE)

比较:

视图:通常用于分解大型查询,无需在临时表中复制或存储数据,数据库中可以重复使用。

 CREATE VIEW vwMyView AS
SELECT
EmployeeID, COUNT(*) AS NumOrders, MAX(OrderDate) AS MaxDate
FROM Orders
GROUP BY EmployeeID
GO SELECT
e.EmployeeID, oe.NumOrders, oe.MaxDate, e.ReportsTo AS ManagerID,
om.NumOrders, om.MaxDate
FROM
Employees AS e
INNER JOIN vwMyView AS oe ON e.EmployeeID = oe.EmployeeID
INNER JOIN vwMyView AS om ON e.ReportsTo = om.EmployeeID

派生表:只能在派生表所在的语句中访问它们,使查询变得更难以阅读和维护(若同一个批处理中要多次使用派生表,必须复制粘贴派生表)

 SELECT
e.EmployeeID, oe.NumOrders, oe.MaxDate, e.ReportsTo AS ManagerID,
om.NumOrders, om.MaxDate
FROM
Employees AS e
INNER JOIN
(SELECT EmployeeID, COUNT(*), MAX(OrderDate)
FROM Orders
GROUP BY EmployeeID) AS oe(EmployeeID, NumOrders, MaxDate)
ON e.EmployeeID = oe.EmployeeID
LEFT JOIN
(SELECT EmployeeID, COUNT(*), MAX(OrderDate)
FROM Orders
GROUP BY EmployeeID) AS om(EmployeeID, NumOrders, MaxDate)
ON e.ReportsTo = om.EmployeeID

CET:提升了 T-SQL 的可读性(就像视图一样),在同一个批处理后紧跟的查询中多次使用,不会在内部创建临时表或虚拟表

 ;WITH EmpOrdersCTE (EmployeeID, NumOrders, MaxDate) AS
(
SELECT EmployeeID, COUNT(*), MAX(OrderDate)
FROM Orders
GROUP BY EmployeeID
) SELECT
e.EmployeeID, oe.NumOrders, oe.MaxDate,
e.ReportsTo AS ManagerID, om.NumOrders, om.MaxDate
FROM
Employees AS e
INNER JOIN EmpOrdersCTE oe ON e.EmployeeID = oe.EmployeeID
LEFT JOIN EmpOrdersCTE om ON e.ReportsTo = om.EmployeeID
上一篇:【iOS开发-56】案例BUG:button的enabled、控件的userInteractionEnabled以及两种提示框UIAlert和UIActionSheet


下一篇:如何使用windows版Docker并在IntelliJ IDEA使用Docker运行Spring Cloud项目