在 INSERT SELECT 语句中如何忽略中间列？

Question

对于一个“插入...选择”语句，是否有一种方法可以忽略在选择语句中需要的但应该被插入的变量？

我正在使用rank函数来选择要插入的数据。不幸的是，rank函数不能在where子句中调用。

insert into target_table(v1
                        ,v2
                        ,v3
                        )
select v1
      ,v2
      ,v3
      ,RANK() over (partition by group_col
                    order by order_col desc
                   ) as my_rank
from source_table
where my_rank > 1

结果是以下错误：

Msg 121，级别15，状态1，第1行插入语句的选择列表包含比插入列表更多的项。 SELECT值的数量必须与INSERT列的数量匹配。

我知道可以使用临时表来完成，但如果可能的话，我希望将其保留在单个语句中。

- Chip McCormick

Answer 1

将主查询包装在子查询中。

INSERT INTO target_table
    (v1, v2, v3)
    SELECT q.v1, q.v2, q.v3
        FROM (SELECT v1, v2, v3, 
                     RANK() OVER (PARTITION BY group_col ORDER BY order_col DESC) AS my_rank
                  FROM source_table) q
        WHERE q.my_rank > 1;

对于SQL Server 2005+，你也可以使用CTE：

WITH cteRank AS (
    SELECT v1, v2, v3, 
           RANK() OVER (PARTITION BY group_col ORDER BY order_col DESC) AS my_rank
        FROM source_table
)
INSERT INTO target_table
    (v1, v2, v3)
    SELECT v1, v2, v3
        FROM cteRank
        WHERE my_rank > 1;

- Joe Stefanelli

---

希望避免在子查询中重复所有内容，但如果没有其他更好的建议，我将接受这个方案。 - Chip McCormick

@ChipMcCormick - 你必须使用CTE或派生表来使你的WHERE子句工作。你不能在WHERE中引用列别名，也不能直接在WHERE中引用RANK()。 - Martin Smith