跨多个代码块重用 WITH 表定义

Question

我有一组包含历史记录的表，最新记录标记为当前 = 1 和已删除 = 0。

我只想使用当前数据运行查询，我想定义一次，然后在多个位置使用。

我使用 WITH 语句定义了一组表。 然后，我创建了一组块来创建临时表。

DECLARE
@first_order_date nvarchar(50) = '2018-01-01'
,@second_order_date nvarchar(50) = '2019-01-01'
;WITH
customer AS (SELECT customer_id FROM d_customer WHERE current = 1 AND deleted = 0),
supplier AS (SELECT supplier_id FROM d_supplier WHERE current = 1 AND deleted = 0),
orderz AS (SELECT order_id, customer_id, supplier_id, order_date FROM d_order WHERE current = 1 AND deleted = 0)
BEGIN
SELECT 
* 
INTO
#first_order
FROM 
orderz
WHERE
order_date >= @first_order_date
END
BEGIN
SELECT 
* 
INTO
#second_order
FROM 
orderz
WHERE
order_date >= @second_order_date
END

我收到错误：无效的对象名称"orderz"。

如果输入代码：

WITH orderz AS (SELECT order_id, customer_id, supplier_id, order_date FROM d_order WHERE current = 1 AND deleted = 0)

在每个块内，即：

BEGIN
WITH 
orderz AS (SELECT 
order_id, 
customer_id, 
supplier_id, 
order_date            
FROM
d_order
WHERE 
current = 1 AND 
deleted = 0)
SELECT 
* 
INTO
#first_order
FROM 
orderz
WHERE
order_date >= @first_order_date
END
BEGIN
WITH 
orderz AS (SELECT 
order_id, 
customer_id, 
supplier_id, 
order_date 
FROM
d_order         
WHERE 
current = 1 AND 
deleted = 0)
SELECT 
* 
INTO
#second_order
FROM 
orderz
WHERE
order_date >= @second_order_date
END

这有效，但如您所见，我不得不在多个地方添加 WITH 语句，因此难以维护。

任何帮助，非常感谢。

Answer 1

公用表表达式只能在单个语句的范围内使用：

指定临时命名结果集，称为公用表表达式 (CTE(。这是从一个简单的查询派生的，并在单个 SELECT、INSERT、UPDATE、DELETE 或 MERGE 语句的执行范围内定义。

但是，您可以做的是改为创建视图，或者创建内联表值函数。

使用视图：

CREATE VIEW customer AS 
SELECT customer_id 
FROM d_customer 
WHERE current = 1 
AND deleted = 0
GO
CREATE VIEW supplier AS 
SELECT supplier_id 
FROM d_supplier 
WHERE current = 1 
AND deleted = 0
GO
CREATE VIEW orderz AS 
SELECT order_id, customer_id, supplier_id, order_date 
FROM d_order 
WHERE current = 1 
AND deleted = 0
GO

与视图相比，使用内联表值函数可能具有性能优势，具体取决于使用情况。下面是一个示例(注意：括号是必需的(：

CREATE FUNCTION dbo.customer()
RETURNS TABLE
AS
RETURN 
(
SELECT customer_id 
FROM d_customer 
WHERE current = 1 
AND deleted = 0
);
GO

你像这样使用它：(注意：这里也需要括号(

SELECT *
FROM dbo.customer()

Answer 2

由于 CTE 需要在单个语句的作用域中使用，因此无法在两个单独的 select 语句中调用它。 您是否考虑过将 CTE 替换为更多临时表定义？

DECLARE
@first_order_date nvarchar(50) = '2018-01-01'
,@second_order_date nvarchar(50) = '2019-01-01'

DROP TABLE IF EXISTS #customer;
SELECT customer_id into #customer FROM d_customer WHERE current = 1 AND deleted = 0;
DROP TABLE IF EXISTS #supplier
SELECT supplier_id INTO #supplier FROM d_supplier WHERE current = 1 AND deleted = 0;
DROP TABLE IF EXISTS #orderz;
SELECT order_id, customer_id, supplier_id, order_date INTO #orderz FROM d_order 
WHERE current = 1 AND deleted = 0;
BEGIN
SELECT 
* 
INTO
#first_order
FROM 
#orderz
WHERE
order_date >= @first_order_date
END
BEGIN
SELECT * 
INTO
#second_order
FROM 
#orderz
WHERE
order_date >= @second_order_date
END`

使用此解决方案，您只需定义一次 #orderz，并且由于每次运行脚本时都会首先删除它，因此您将仅使用当前数据。