数据库：表表达式

表表达式(Table Expression)

又被称之为“表子查询”，是一个命名的查询表达式，代表一个有效的关系表。Microsoft SQL Server支持4种类型的表表达式：派生表、公用表表达式（CTE）、视图和内嵌表值函数（内嵌TVF）。

有效定义任何类型表表达式的查询必须满足3个要求：
1. 无法保证顺序。

表表达式用于代表一个关系表，并且关系表中的行无法保证顺序。因此，标准 SQL 不允许 ORDER BY 子句出现在定义表表达式的查询中，除非 ORDER BY 用于展示之外的其他目的。例如使用具有 TOP 或 OFFSET-FETCH 与 ORDER BY 的查询定义一个表表达式， ORDER BY 仅是用于与筛选相关的目的，而不是通常的展示目的。如果表表达式的外部查询没有用于展示的 ORDER BY，则输出无法保证按特定顺序返回。

2. 所有列都必须具有名称。

在定义表表达式的查询中，必须为 SELECT 列表中的所有表达式分配列别名。

3. 所有列名都必须是唯一的。

多个列具有相同名称的表表达式是无效的。
注：所有这3个要求的事实是，表表达式应当表示一个关系。所有关系属性必须具有名称，所有属性名称必须唯一，并且关系的主体是一个无序的元组集合。

 

派生表（Derived）

出现在查询的FROM子句中：必须有表别名

FROM (SELECT clause) [AS] derived_table_alias

只能用于外部查询，必须是独立的，不能是相关的

最大特点：可以在外部WHERE/ON/GROUP clause中使用派生表中的列别名。

SELECT problem.Title, [max].MaxReward
FROM (
      SELECT Author, MAX(Reward) AS MaxReward
      FROM TProblem
      GROUP BY Author
) [max]INNER JOIN TProblem [problem]
ON [max].Author = [problem].Author AND problem.Reward = [max].MaxReward

 

公用表达式（Common Table Expression）

WITH cte_name_1[column_1, column_2, ....]
AS(SELECT clause_1),
cte_name_2[column_1, column_2, ....]
AS(SELECT clause_2),
......

优势：可以一次定义，反复使用

 

临时表

以#开头，存储在tempdb中，会话结束时系统自动清理

• 局部：#table_name，当前会话结束时清除
• 全局：##table_name，所有会话（用户）可见，直到全部用户会话结束时清除

-- 建一个临时表
CREATE TABLE #rn(
rId INT IDENTITY,
pId INT)

-- 插入数据，生成自增rId
INSERT #rn(pId)
SELECT Id FROM TProblem ORDER BY Reward

-- JOIN获取TProblem完整数据
SELECT TProblem.Id, Title, Author FROM #rn 
JOIN TProblem ON #rn.pId = TProblem.Id
WHERE #rn.rId BETWEEN 4 AND 6

 

 

表变量

把表当做一个变量存储使用，一样存储在tempdb中

DECLARE @TVariable TABLE(Id INT)
INSERT @TVariable VALUES(986)
SELECT * FROM @TVariable

 

文章转载自：快速开发平台– 云微平台
地址：https://www.hocode.com/