;WITH cte
AS (SELECT ROW_NUMBER() OVER (PARTITION BY Col1,Col2,Col3
ORDER BY ( SELECT 0)) RN
FROM #MyTable)
DELETE FROM cte
WHERE RN > 1
另一个例子是; THROW
为什么有一个’;’ Tsql语句的开始
更新1：
请注意,我在问”’在声明的开头.这个问题与这个问题不重复
When should I use semicolons in SQL Server?
更新2：
@MartinSmith答案是有道理的.
为了确保我们有一个完整的答案这个职位,请考虑这个受人尊敬的文章：
http://www.sommarskog.se/error_handling/Part1.html#jumpTHROW
At this point you might be saying to yourself: he must be pulling my
legs,did Microsoft really call the command ;THROW? Isn’t it just
THROW? True,if you look it up in Books Online,there is no leading
semicolon. But the semicolon must be there. Officially,it is a
terminator for the prevIoUs statement,but it is optional,and far
from everyone uses semicolons to terminate their T‑sql statements.
我同意@MartinSmith的回答,但似乎这个事情正在采取一些相当极端的水平.
通常,在存储过程中,THROW是由其自己的行声明的. sql开发人员不仅仅是合并这样的sql行,而且想要分号.
对我来说,有更多的机会人们意外丢弃一个表,而不是将“THROW”语句与另一行Tsql混合
这个例子在上面引用的这个例子是极端的,很少发生的吗？或者我在这里错过了一点？
解决方法
一个例外是MERGE语句(确实需要一个终止的半冒号)以及WITH WITH THROW之前的语句
所以对于StackOverflow来说,这是一个有点防御性的做法,如果OP(或者将来的读者)将它粘贴到一些现有的批处理中间,这些批处理在前面的语句中没有必要的半冒号,然后抱怨它不起作用他们收到以下错误.
Incorrect Syntax near the keyword ‘with’. If this statement is a
common table expression,an xmlnamespaces clause or a change tracking
context clause,the prevIoUs statement must be terminated with a
semicolon.
在前面的语句以分号终止的情况下,额外的语句不会有任何损害.它只是被视为一个空的声明.
这种做法本身可能会导致问题,尽管在多语句无效的上下文中使用CTE.例如在WITH之前插入分号可能会破坏它.
CREATE VIEW V1
AS
WITH T(X)
AS (SELECT 1)
SELECT *
FROM T;
类似地,THROW盲目插入前导的半结肠也可能引起问题.
IF @i IS NULL ;THROW 50000,'@i IS NULL',1;
Incorrect Syntax near ‘;’.
我已经修复了the example you give in your question,并将其改为
;
--Ensure that any immediately preceding statement is terminated with a semicolon above
WITH cte
AS (SELECT ROW_NUMBER() OVER (PARTITION BY Col1,Col3
ORDER BY ( SELECT 0)) RN
FROM #MyTable)
DELETE FROM cte
WHERE RN > 1;
