TSQL中能否复用嵌套查询结果？避免重复编写嵌套SELECT语句

问题描述 #

能否在TSQL中创建可复用的嵌套SELECT语句？例如类似如下写法：

Select(
   Select1(reusable)
   Select2(where Select1)
   Select3(where Select1)
   Select4(where Select1)
)

还是必须在每个下层查询中重复编写相同的SELECT语句来获取所需值？我尝试了如下写法但始终报错：

(SELECT TOP (1) LOCATION    FROM Address AS d   
WHERE(RECID = trans.Address) ) as loc,

(SELECT TOP (1) CITY WHERE
(loc = other.recid)) as CITY

解决方案 #

在TSQL里，不需要重复编写相同的子查询，有几种实用方法实现复用：

• CTE（公共表表达式）：这是单次查询内复用子查询的最优方案，定义一次后可在整个查询中多次引用，写法简洁易维护：

WITH ReusableLoc AS (
    SELECT TOP (1) LOCATION 
    FROM Address AS d   
    WHERE RECID = trans.Address
)
SELECT 
    rl.LOCATION AS loc,
    (SELECT TOP (1) CITY FROM other WHERE other.recid = rl.LOCATION) AS CITY
FROM ReusableLoc rl;

• 临时表/表变量：如果需要在多个独立查询中复用结果，临时表或表变量更合适：

-- 使用临时表
SELECT TOP (1) LOCATION INTO #TempLocation
FROM Address AS d   
WHERE RECID = trans.Address;

SELECT 
    LOCATION AS loc,
    (SELECT TOP (1) CITY FROM other WHERE recid = LOCATION) AS CITY
FROM #TempLocation;

DROP TABLE #TempLocation; -- 临时表用完建议手动清理

-- 使用表变量
DECLARE @TempLocation TABLE (LOCATION INT); -- 根据实际字段类型调整
INSERT INTO @TempLocation
SELECT TOP (1) LOCATION 
FROM Address AS d   
WHERE RECID = trans.Address;

SELECT 
    LOCATION AS loc,
    (SELECT TOP (1) CITY FROM other WHERE recid = LOCATION) AS CITY
FROM @TempLocation;

你之前的写法报错，核心原因是SQL的执行顺序限制：SELECT列表中定义的列别名（比如loc），无法在同层级的其他子查询中直接引用。必须将可复用的子查询结果提前定义（如CTE、临时表），才能在后续逻辑中调用。

内容的提问来源于stack exchange，提问作者Tweene