公用表表达式，为什么要分号?

本文介绍了公用表表达式，为什么要分号?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

通常在SQL Server Common Table Expression 子句中，语句前有分号，像这样:

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.

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