为什么这不起作用：使用 "With ROW_NUMBER" 插入 SQL 语句。错误是关键字 'With' 附近的语法不正确

Question

我使用这种语法分别在组件中工作，但当我一起使用它时，它停止工作，我得到一个语法错误:关键字'With'附近的语法不正确。我不知道为什么。

if OBJECT_ID ('tempdb..#Assignments') is not null 
drop table #Assignments
create table #Assignments
(
SerialNumber varchar(24) not null
)
insert into #Assignments (SerialNumber)
With Row_Number as 
(
select SerialNumber,row_number() 
over (partition by serialNumber order by serialnumber) as Row_Num
from Monitor  
where SerialNumber IS NOT NULL
) 
select SerialNumber from Row_Number where Row_Num > 1

Answer 1

语法不合法。当你使用CTE时，WITH是语句的第一部分。

if OBJECT_ID ('tempdb..#Assignments') is not null 
drop table #Assignments;
create table #Assignments
(
SerialNumber varchar(24) not null
);
With Row_Number as 
(
select SerialNumber,row_number() over (partition by serialNumber order by serialnumber) as Row_Num
from Monitor  
where SerialNumber IS NOT NULL
) 
insert into #Assignments (SerialNumber)
select SerialNumber from Row_Number where Row_Num > 1;

旁注:当您使用CTE时，请确保在语句后包含;。必须终止WITH之前的语句，否则会出现语法错误。