在INSERT内部时，对CASE的SQL语句感到困惑

I have the following SQL statement (that is intended for SQL Server):

我有以下SQL语句(用于SQL Server):

INSERT INTO t1 (c1, c2) 
VALUES (1, CASE WHEN (SELECT MAX(c2) FROM t1 AS maxV) IS NOT NULL THEN maxV+1 ELSE 1 END);

I get an error: "Invalid column name 'maxV'"

我得到一个错误:“无效列名'maxV'”

Why?

为什么?

3 个解决方案

#1

Try using COALESCE:

尝试使用合并:

INSERT INTO t1 (c1, c2) 
SELECT 1, COALESCE(MAX(c2), 0) + 1
FROM t1

#2

Try this...

试试这个…

INSERT INTO t1 (c1, c2) 
VALUES (
    1, 
    CASE 
        WHEN (SELECT MAX(c2) FROM t1) IS NOT NULL 
        THEN (SELECT MAX(c2)+1 FROM t1)
        ELSE 1 
    END);

#3

Another way of doing it...

另一种方法是……

INSERT INTO t1 (c1, c2) 
VALUES (1, ISNULL(SELECT MAX(c2) FROM t1, 0)+1);

#1