如何在单查询中从同一输入表生成两个匹配结果表？

问题根源 #

SQL Server 中，CTE（公共表表达式）的作用域仅限紧接着的第一条数据操作语句（SELECT/INSERT/UPDATE/DELETE等），后续的第二条SELECT无法访问之前定义的CTE，这就是你收到「无效对象名」报错的原因。

修正方法 #

推荐用临时表或表变量先存储CTE的匹配结果，后续多个SELECT直接从临时表读取数据，既保证逻辑复用，又能生成独立的结果集。

示例代码

假设你原本的CTE逻辑如下：

WITH male_sample AS (
    SELECT id, name, age FROM test WHERE gender = '男'
),
female_sample AS (
    SELECT id, name, age FROM test WHERE gender = '女'
),
match_pairs AS (
    SELECT m.id AS male_id, m.name AS male_name, f.id AS female_id, f.name AS female_name, m.age
    FROM male_sample m
    JOIN female_sample f ON m.age = f.age
)
SELECT * FROM match_pairs;

修改为临时表方案后：

-- 1. 将匹配结果存入临时表
WITH male_sample AS (
    SELECT id, name, age FROM test WHERE gender = '男'
),
female_sample AS (
    SELECT id, name, age FROM test WHERE gender = '女'
),
match_pairs AS (
    SELECT m.id AS male_id, m.name AS male_name, f.id AS female_id, f.name AS female_name, m.age
    FROM male_sample m
    JOIN female_sample f ON m.age = f.age
)
SELECT * INTO #temp_match FROM match_pairs;

-- 2. 生成独立的男性表结果
SELECT DISTINCT male_id AS id, male_name AS name, age FROM #temp_match;

-- 3. 生成独立的女性表结果
SELECT DISTINCT female_id AS id, female_name AS name, age FROM #temp_match;

-- 4. 保留同年龄匹配对结果
SELECT * FROM #temp_match;

-- 可选：手动清理临时表（SQL Server会在会话结束后自动删除）
DROP TABLE IF EXISTS #temp_match;

替代方案（无需临时表）

如果不想用临时表，可通过UNION ALL给结果加标识列，一次性返回所有数据，后续在应用层区分不同结果集：

WITH male_sample AS (
    SELECT id, name, age FROM test WHERE gender = '男'
),
female_sample AS (
    SELECT id, name, age FROM test WHERE gender = '女'
),
match_pairs AS (
    SELECT m.id AS male_id, m.name AS male_name, f.id AS female_id, f.name AS female_name, m.age
    FROM male_sample m
    JOIN female_sample f ON m.age = f.age
)
-- 男性表（带标识）
SELECT '男性表' AS result_type, male_id AS id, male_name AS name, age FROM match_pairs
UNION ALL
-- 女性表（带标识）
SELECT '女性表' AS result_type, female_id AS id, female_name AS name, age FROM match_pairs
UNION ALL
-- 匹配对表（带标识）
SELECT '匹配对' AS result_type, NULL AS id, NULL AS name, age, male_id, male_name, female_id, female_name FROM match_pairs;

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