sql - "Incorrect syntax"使用公用表表达式时

Question

WITH list_dedup (Company, duplicate_count) AS
(
     SELECT
         *,
         ROW_NUMBER() OVER (PARTITION BY Company ORDER BY Email) AS 'RowNumber'
     FROM
         Travels
)

错误 :

Msg 102, Level 15, State 1, Line 7
Incorrect syntax near ')'.

Answer 1

您缺少公用表表达式的最终选择( 在 CTE 的定义之后):

WITH list_dedup  (Company,duplicate_count) As
(
  select *,
         ROW_NUMBER() OVER (PARTITION BY Company ORDER by Email) As "RowNumber"
  From Travels
)
select * 
from list_dedup;

但这不会因为 CTE 被定义为具有 两个 列(通过 WITH list_dedup (Company,duplicate_count) )，但您在 CTE 中的选择至少返回三列(公司、电子邮件、行号)。您需要调整 CTE 的列定义，或者完全忽略它:

WITH list_dedup As
(
  select *,
         ROW_NUMBER() OVER (PARTITION BY Company ORDER by Email) As "RowNumber"
  From Travels
)
select *  
from list_dedup;

As "RowNumber"在定义列列表时，内部选择也没有意义，因为 CTE 定义定义了列名。 CTE 内部使用的任何别名在其外部都将不可见(如果 CTE 列在 with .. (...) as 部分中指定)。