SQL Server:NULL时添加max(column_value)1

我有两个表,即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)

2 years ago

试试这个:

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

Demo here