MSSQL中 利用局部临时表实现数据处理

介绍

在 MSSQL 数据库中，局部临时表是一种非常有用的功能。临时表是一个存储单元，可以在其生命周期内存储临时数据，只有创建它们的会话才能看到并访问该表。

局部临时表只在当前作用域（也就是当前会话）内存在。一旦会话结束，表就会自动删除。这种临时表通常被用于动态地构造中间结果集，以便对数据进行处理。

创建局部临时表

在 MSSQL 中，使用 CREATE TABLE 语句可以创建一张全局表，而使用 CREATE TABLE #temp_table 语句则可以创建一张局部临时表。

下面是创建一张简单的局部临时表的 SQL 代码：

CREATE TABLE #tempTable (
    Id INT,
    Name VARCHAR(50),
    DateOfBirth DATE
);

这段代码创建了名为 #tempTable 的一张局部临时表。这张表包含三列的数据：Id、Name 和 DateOfBirth。

向局部临时表插入数据

向局部临时表插入数据的方式跟向任何其他表插入数据的方式是一样的。你可以使用 INSERT INTO 语句插入单行数据，如下所示：

INSERT INTO #tempTable (Id, Name, DateOfBirth)
VALUES (1, 'John Smith', '1990-01-01');

如果你需要插入多行数据，可以使用 INSERT INTO … SELECT 语句：

INSERT INTO #tempTable (Id, Name, DateOfBirth)
SELECT Id, Name, DateOfBirth
FROM SomeOtherTable
WHERE SomeColumn = 'SomeValue'

这会把 SomeOtherTable 表中 SomeColumn 等于 SomeValue 的所有行的 Id、Name 和 DateOfBirth 数据插入到 #tempTable 表中。

查询局部临时表中的数据

你可以使用 SELECT 语句查询局部临时表中的数据，像查询任何其他表一样。下面是一个简单的 SELECT 例子：

SELECT *
FROM #tempTable;

这将返回 #tempTable 表中所有行的所有列。

删除局部临时表

当你不再需要使用局部临时表时，应该将其删除。可以使用 DROP TABLE 语句删除表，代码如下：

DROP TABLE #tempTable;

注意，MSSQL 在处理结束后自动删除了所有临时表，但还是推荐在使用完临时表后立即将其删除。这可以释放更多的数据库资源。

示例应用

下面通过一个示例展示如何使用局部临时表。

假设你运营着一家网购商店，并且有一个包含所有订单的 orders 表。这个表包含许多列，包括订单 ID、产品名称、顾客 ID、下单日期、发货日期、订单状态等等。

你现在需要从该订单表中找出所有已发货但仍未收到的订单。实现步骤如下：

在使用该临时表之前，需要先创建 orders 表，并向其插入一些测试数据，以便我们能够测试临时表的使用效果。

-- 创建 orders 表
CREATE TABLE orders (
    orderId INT,
    productId INT,
    customerId INT,
    purchaseDate DATE,
    shippingDate DATE,
    deliveryDate DATE,
    status VARCHAR(50)
);
-- 向 orders 表插入一些测试数据
INSERT INTO orders (orderId, productId, customerId, purchaseDate, shippingDate, deliveryDate, status)
VALUES
    (1, 1, 1, '2020-01-01', '2020-01-02', '2020-01-10', 'delivered'),
    (2, 2, 1, '2020-01-02', '2020-01-03', '2020-01-08', 'delivered'),
    (3, 3, 2, '2020-01-02', '2020-01-04', '2020-01-11', 'delivered'),
    (4, 4, 3, '2020-01-03', '2020-01-06', null, 'shipped'),
    (5, 5, 4, '2020-01-04', '2020-01-07', null, 'shipped');

现在，我们将使用局部临时表查询出所有已发货但仍未送达的订单。

首先，我们需要 SELECT 找出所有已发货的订单，并将其保存到局部临时表中。

-- 使用 SELECT 找出所有已发货的订单，并将其保存到局部临时表 #shippedOrders 中。
SELECT *
INTO #shippedOrders
FROM orders
WHERE shippingDate IS NOT NULL AND deliveryDate IS NULL;

此命令将找出所有已发货的订单，并将它们插入 #shippedOrders 中。

然后，我们可以使用 SELECT 语句查询该临时表，并找出所有尚未送达的订单。

-- 使用 SELECT 找出所有尚未送达的订单。
SELECT *
FROM #shippedOrders
WHERE deliveryDate IS NULL;

此命令将输出所有已发货但尚未送达的订单。

最后，完成操作后我们需要清理该临时表。

-- 清理临时表
DROP TABLE #shippedOrders;

此命令将清空并删除作为 #shippedOrders 的局部临时表。

总结

局部临时表是一种非常有用的功能，可以让我们动态地构造中间结果集以便对数据进行处理。在 MSSQL 中，创建临时表的过程与创建任何其他表的过程相同。我们可以使用 INSERT INTO 语句插入数据，使用 SELECT 语句查询数据，使用 DROP TABLE 语句删除表。在一个场景中，我们可以使用这个功能，找出所有已发货但仍未送达的订单。