如何在交叉连接子查询中创建IDENTITY列？SQL优化方案咨询

问题分析 #

你遇到的语法错误是因为IDENTITY函数不能在子查询中直接使用——它仅允许在SELECT INTO或者CREATE TABLE语句里用来定义标识列，子查询里没法用它生成自增序号。

更简洁的实现方案 #

不需要临时表，直接用ROW_NUMBER()窗口函数生成连续序号，就能替代原来的IDENTITY逻辑。这样可以把整个逻辑合并成一个INSERT语句，不用额外创建临时表。

完整代码示例 #

CREATE TABLE #A (A_ID INT)
CREATE TABLE #B (B_ID INT)
CREATE TABLE #C (C_ID INT, A_ID INT, B_ID INT)
CREATE TABLE #D (C_ID INT, A_ID INT, B_ID INT)

INSERT INTO #A VALUES (1),(2)
INSERT INTO #B VALUES (1),(2)
INSERT INTO #C VALUES (1,1,1),(2,1,2)

-- 直接用ROW_NUMBER生成序号，无需临时表
INSERT INTO #D (C_ID, A_ID, B_ID)
SELECT 
    ISNULL(c.C_ID, -1 * temp.N), 
    temp.A_ID, 
    temp.B_ID
FROM (
    SELECT 
        ROW_NUMBER() OVER (ORDER BY a.A_ID, b.B_ID) AS N,
        a.A_ID, 
        b.B_ID
    FROM #A a 
    CROSS JOIN #B b
) temp
LEFT OUTER JOIN #C c 
    ON c.A_ID = temp.A_ID 
    AND c.B_ID = temp.B_ID

SELECT * FROM #D

执行结果 #

C_ID A_ID B_ID
--------------
1     1   1
-2    2   1
2     1   2
-4    2   2

补充说明 #

• ROW_NUMBER()会按指定的排序规则（这里是a.A_ID, b.B_ID）生成连续的整数序号，和原来IDENTITY的效果一致。
• 如果你不需要固定的排序顺序，可以把ORDER BY换成ORDER BY (SELECT NULL)，让数据库自行选择最优顺序生成序号。

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