我有两个表，即Test2和Test3 .

我在 Test2 表中有10条记录 . 并且 Test3 表中没有记录 .

在表Test2中，我在 ColumnID 列中有一些空值 .

我想通过从表 Test2 AND 中选择记录来将记录插入到表 Test3 中，当存在空值时，我只想添加最大列 ColumnID 加1 .

Table ：Test2

Create table Test2
(
  ColumnID int
);

Insertion ：

INSERT INTO Test2 values(1);
INSERT INTO Test2 values(2);
INSERT INTO Test2 values(3);
INSERT INTO Test2 values(5);
INSERT INTO Test2 values(8);
INSERT INTO Test2 values(null);
INSERT INTO Test2 values(null);
INSERT INTO Test2 values(null);
INSERT INTO Test2 values(null);
INSERT INTO Test2 values(null);

Expected Result ：

表：测试3

ColumnID
----------------
1
2
3
5
8
9  --Increament by 1 to max of id
10 --increment by 1 to max of id  
11
12
13

尝试：1

INSERT INTO Test3
SELECT Case WHEN ColumnID IS NOT NULL then ColumnID  else max(ColumnID)+1 end as Column_ID
from Test2
group by ColumnID;

尝试：2

DECLARE @count int
DECLARE @i int = 1
SELECT @count = COUNT(*) FROM Test2;

WHILE (@i <= @count)
BEGIN
    INSERT INTO Test3 
    SELECT Case WHEN ColumnID IS NOT NULL then ColumnID  else max(ColumnID)+1 end as Column_ID
    from Test2
    group by ColumnID;
    set @i = @i + 1;
END
GO

1 回答

试试这个：

INSERT INTO Test3
SELECT COALESCE(ColumnID, 
                ROW_NUMBER() OVER (PARTITION By ColumnID 
                                   ORDER BY ColumnID) +  
                MAX(ColumnID) OVER ()) 
FROM Test2

Demo here

回复于 2024-04-19T15:13:07+08:00

SQL Server：NULL时添加max（column_value）1

尝试：1

尝试：2

1 回答

相关问题