如何让SQL Server忽略CTE中的重复列名？

首先明确一点：SQL Server的CTE（公用表表达式）必须要求结果集列名完全唯一，这是设计上的硬性要求，没有内置开关可以跳过这个检查——毕竟CTE是一个可重复引用的命名结果集，重复列名会导致后续引用时出现歧义。而普通SELECT查询允许返回同名列，是因为它只是直接输出结果（客户端通常会自行处理列名冲突，比如保留最后一个出现的列），和CTE的定位不同。

既然你不想手动枚举数百列，动态生成去重后的列列表是最可行的方案，因为关联列的值始终相等，我们只需要为每个重复列名保留一个实例即可。下面是具体实现：

方法：用动态SQL自动生成去重列 #

我们可以通过查询系统视图获取所有涉及表的列，自动去重后拼接成SELECT语句，代替SELECT *。

示例代码 #

DECLARE @columns NVARCHAR(MAX);
DECLARE @sql NVARCHAR(MAX);

-- 第一步：生成去重的列名列表，优先保留主表（CRROUTE）的列，再依次保留其他表的列
WITH ColumnsCTE AS (
    SELECT 
        c.name AS column_name,
        -- 给表排序，确保主表的列优先被选中
        ROW_NUMBER() OVER (
            PARTITION BY c.name 
            ORDER BY 
                CASE t.name 
                    WHEN 'CRROUTE' THEN 1 
                    WHEN 'crewleg' THEN 2 
                    WHEN 'legmain' THEN 3 
                    WHEN 'legtimes' THEN 4 
                END
        ) AS row_num
    FROM sys.tables t
    JOIN sys.columns c ON t.object_id = c.object_id
    -- 指定查询涉及的表
    WHERE t.name IN ('CRROUTE', 'crewleg', 'legmain', 'legtimes')
)
-- 拼接列名，只保留每个列名的第一个实例
SELECT @columns = STRING_AGG(QUOTENAME(column_name), ', ')
FROM ColumnsCTE
WHERE row_num = 1
ORDER BY column_name;

-- 第二步：构建完整的CTE查询语句
SET @sql = N'WITH cteRoute AS (
    SELECT ' + @columns + N'
    FROM CRROUTE cr
    JOIN crewleg cl on (cr.CDATE = cl.CDATE AND cr.CROUTE = cl.croute)
    LEFT JOIN legmain lm on (lm.DAY = cl.DAY AND lm.DEP = cl.DEP and lm.CARRIER = cl.CARRIER and lm.FLT = cl.FLT and lm.LEGCD = cl.LEGCD )
    LEFT JOIN legtimes lt on (lt.DAY = lm.DAY AND lt.DEP = lm.DEP and lt.CARRIER = lm.CARRIER and lt.FLT = lm.FLT and lt.LEGCD = lm.LEGCD )
    WHERE cr.cdate = 14000 and cr.croute = 138266498116
)
-- 这里可以根据需要修改后续查询逻辑
SELECT * FROM cteRoute;';

-- 执行动态SQL
EXEC sp_executesql @sql;

代码说明 #

1. 系统视图查询：通过sys.tables和sys.columns获取所有涉及表的列信息，避免手动维护列列表。
2. 列去重逻辑：用ROW_NUMBER()按表的优先级排序，确保每个列名只保留一个实例（优先取主表CRROUTE的列，保证数据一致性）。
3. 动态拼接SQL：把去重后的列列表插入到原查询中，生成合法的CTE语句。

其他替代思路 #

如果不想用动态SQL，还有一个折衷方案：直接使用子查询代替CTE，然后在外部查询中用SELECT *——不过子查询本身其实也会有重复列，但如果只是一次性查询（不需要重复引用结果集），可以直接写：

SELECT *
FROM (
    SELECT * 
    FROM CRROUTE cr
    JOIN crewleg cl on (cr.CDATE = cl.CDATE AND cr.CROUTE = cl.croute)
    LEFT JOIN legmain lm on (lm.DAY = cl.DAY AND lm.DEP = cl.DEP and lm.CARRIER = cl.CARRIER and lm.FLT = cl.FLT and lm.LEGCD = cl.LEGCD )
    LEFT JOIN legtimes lt on (lt.DAY = lm.DAY AND lt.DEP = lm.DEP and lt.CARRIER = lm.CARRIER and lt.FLT = lm.FLT and lt.LEGCD = lm.LEGCD )
    WHERE cr.cdate = 14000 and cr.croute = 138266498116
) AS subquery;

不过这种方法虽然能执行，但返回的结果仍会有重复列，客户端处理时可能会有问题（比如某些工具会自动重命名重复列，或者忽略重复列），所以更推荐前面的动态SQL方案。

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