公共表表达式，为什么要用分号？ [英] Common Table Expression, why semicolon?

问题描述

通常在 SQL Server 公用表表达式子句中，语句前有分号，例如：

Usually in SQL Server Common Table Expression clause there is semicolon in front of the statement, like this:

;WITH OrderedOrders AS --semicolon here
(
    SELECT SalesOrderID, OrderDate,
    ROW_NUMBER() OVER (ORDER BY OrderDate) AS 'RowNumber'
    FROM Sales.SalesOrderHeader 
) 
SELECT * 
FROM OrderedOrders 
WHERE RowNumber BETWEEN 50 AND 60

为什么？

推荐答案

• 为避免歧义因为WITH可以在其他地方使用
  
  .. FROM..WITH（NOLOCK）..
RESTORE..WITH MOVE ..


• 在SQL Server中以; 终止语句是可选的


• To avoid ambiguity because WITH can be used elsewhere
  ..FROM..WITH (NOLOCK)..
RESTORE..WITH MOVE..
• It's optional to terminate statements with ; in SQL Server

放在一起，前一个语句必须在WITH / CTE之前终止。为了避免错误，大多数人都使用; WITH ，因为我们不知道CTE之前是什么

Put together, the previous statement must be terminated before a WITH/CTE. To avoid errors, most folk use ;WITH because we don't know what is before the CTE

所以

DECLARE @foo int;

WITH OrderedOrders AS
(
    SELECT SalesOrderID, OrderDate,
...;

与

DECLARE @foo int

;WITH OrderedOrders AS
(
    SELECT SalesOrderID, OrderDate,
...;

MERGE命令具有类似的要求。

The MERGE command has a similar requirement.

这篇关于公共表表达式，为什么要用分号？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！