sql临时表是会话级存储中间结果的工具，用于简化复杂查询、提高性能。其生命周期仅限当前会话，自动删除，区别于永久普通表；创建时使用#（本地）或##（全局），mysql则用temporary关键字；用途包括分解复杂查询、性能优化、存储过程逻辑处理及数据清洗转换；使用时应避免冗余数据、合理建索引、及时删除；与cte相比，临时表适用于多查询共享和索引优化，而cte适合单次查询的简洁场景。
临时表，顾名思义，就是临时存储数据的地方。它在SQL中扮演着非常重要的角色，尤其是在处理复杂查询和存储中间结果时。可以理解为，在你完成最终查询目标前，先搭个临时的“台子”，把一些半成品先放上去，最后再利用这些半成品完成最终目标。
SQL临时表主要用于会话级的数据存储与中间结果处理。它允许你在当前会话中创建和使用表，而这些表在会话结束时会自动删除。这对于存储中间计算结果、简化复杂查询、以及提高查询效率非常有帮助。
如何创建和使用SQL临时表？
创建临时表的方式与创建普通表类似，但需要在表名前加上 # (本地临时表) 或 ## (全局临时表)。本地临时表只对当前会话可见，而全局临时表对所有会话可见，但使用完后最好手动删除。
例如，创建一个本地临时表：
CREATE TEMP table #TempOrders ( OrderID INT, CustomerID INT, OrderDate DATETIME ); -- 或者，在mysql中： CREATE TEMPORARY TABLE TempOrders ( OrderID INT, CustomerID INT, OrderDate DATETIME ); INSERT INTO #TempOrders (OrderID, CustomerID, OrderDate) SELECT OrderID, CustomerID, OrderDate FROM Orders WHERE OrderDate >= '2023-01-01'; SELECT * FROM #TempOrders; -- 完成后，SQL Server会自动删除本地临时表，MySQL则需要手动删除 -- DROP TABLE #TempOrders; -- SQL Server不需要 -- DROP TEMPORARY TABLE TempOrders; -- MySQL 需要
注意，不同数据库系统对临时表的语法可能略有差异，例如MySQL使用TEMPORARY关键字，而SQL Server使用#或##。
临时表与普通表有什么区别？何时应该使用临时表？
最主要的区别在于生命周期和可见性。普通表是永久存在的，除非显式删除，而临时表只存在于当前会话期间。
何时使用临时表呢？
- 复杂查询分解： 当你需要执行涉及多个步骤的复杂查询时，可以将中间结果存储在临时表中，简化后续查询逻辑。想象一下，你要做一个非常复杂的菜，临时表就像你的备菜台，先把各种食材切好、洗好，再开始炒菜，思路会清晰很多。
- 性能优化： 在某些情况下，将中间结果存储在临时表中可以避免重复计算，从而提高查询性能。比如，你有个复杂的计算需要多次用到，与其每次都重新算一遍，不如算一次存起来，下次直接用。
- 存储过程和函数： 临时表在存储过程和函数中非常有用，可以用来存储局部变量或中间结果。
- 数据转换和清洗： 你可能需要对数据进行转换或清洗，临时表可以作为数据处理的“缓冲区”。
临时表在存储过程中如何发挥作用？
存储过程是预编译的sql语句集合，可以接受参数并执行一系列操作。临时表在存储过程中可以用来存储中间结果，简化逻辑，并提高效率。
例如，假设你需要创建一个存储过程，计算每个客户的订单总金额，并将结果存储在一个临时表中：
CREATE PROCEDURE CalculateCustomerOrderTotals AS BEGIN -- 创建临时表存储客户订单总金额 CREATE TABLE #CustomerOrderTotals ( CustomerID INT, TotalAmount DECIMAL(18, 2) ); -- 计算每个客户的订单总金额并插入临时表 INSERT INTO #CustomerOrderTotals (CustomerID, TotalAmount) SELECT CustomerID, SUM(OrderAmount) FROM Orders GROUP BY CustomerID; -- 返回结果 SELECT * FROM #CustomerOrderTotals; -- 删除临时表 DROP TABLE #CustomerOrderTotals; END; -- 执行存储过程 EXEC CalculateCustomerOrderTotals;
在这个例子中，#CustomerOrderTotals 临时表用于存储每个客户的订单总金额，简化了存储过程的逻辑，并且在存储过程执行完毕后自动删除，避免了数据污染。
如何优化临时表的使用以提高查询性能？
- 只存储必要的数据： 避免在临时表中存储不必要的数据，减少存储空间和I/O开销。
- 使用索引： 如果需要对临时表进行频繁的查询，可以考虑在临时表上创建索引，提高查询速度。
- 及时删除临时表： 在不再需要临时表时，及时删除，释放资源。虽然会话结束后会自动删除，但手动删除可以更早地释放资源，尤其是在长时间运行的会话中。
- 避免过度使用临时表： 虽然临时表很有用，但过度使用会增加查询的复杂性，降低可读性。在可以使用子查询或CTE (Common Table Expression) 的情况下，尽量避免使用临时表。
临时表与CTE (Common Table Expression) 有什么区别？应该如何选择？
CTE 是一种命名的临时结果集，可以在单个查询中引用多次。与临时表不同，CTE 只存在于查询执行期间，不会持久化存储。
选择临时表还是 CTE，取决于具体的需求：
- CTE 适用于简单的、只在单个查询中使用的临时结果集。 CTE 更加简洁，易于阅读和维护。
- 临时表适用于复杂的、需要在多个查询中使用的临时结果集。 临时表可以跨多个查询共享数据，并且可以创建索引进行优化。
总的来说，如果你的临时结果集只需要在一个查询中使用，那么 CTE 是一个更好的选择。如果需要在多个查询中使用，或者需要对临时结果集进行索引优化，那么临时表更适合。
记住，SQL 临时表是数据库开发中一个非常实用的工具，掌握它可以帮助你编写更高效、更简洁的SQL代码。
