WITH list_dedup (Company, duplicate_count) AS
(
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY Company ORDER BY Email) AS 'RowNumber'
FROM
Travels
)
错误:
消息 102,级别 15,状态 1,行 7
附近有语法错误 ')'。
WITH list_dedup (Company, duplicate_count) AS
(
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY Company ORDER BY Email) AS 'RowNumber'
FROM
Travels
)
错误:
消息 102,级别 15,状态 1,行 7
附近有语法错误 ')'。
WITH list_dedup (Company,duplicate_count) As
(
select *,
ROW_NUMBER() OVER (PARTITION BY Company ORDER by Email) As "RowNumber"
From Travels
)
<b>select *
from list_dedup;</b>
但是这样做是不行的,因为CTE被定义为有两列(通过WITH list_dedup (Company,duplicate_count)
),但是在CTE内部的select返回至少三列(company,email,rownumber)。你需要调整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
部分中指定)。
您刚刚设置了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
使用list_dedup(公司,重复计数)作为 ( SELECT *, ROW_NUMBER() OVER (PARTITION BY Company ORDER BY Email) AS 'RowNumber' FROM Travels )
从list_dedup中选择*
WITH list_dedup (Company, duplicate_count) AS ( ... ) SELECT * FROM list_dedup
- Martin Smith