CTE查询SQL Server中的解析错误 [英] Parsing error in in CTE query SQL server

问题描述

我已经编写了CTE查询，并且正在Microsoft SQL Server 2008 R2 Management Studio中执行查询：

I have written a CTE query and I am executing the query in Microsoft SQL Server 2008 R2 Management Studio:

WITH DependencyHierarchy(processName, dependProcessName) AS
(
    SELECT
       processName,
       dependProcessName,
       1 as HierarchyLevel
    FROM processDependency

    UNION ALL

    SELECT
        e.processName,
        e.dependProcessName,
        eh.HierarchyLevel + 1 AS HierarchyLevel
    FROM 
        processDependency e
    INNER JOIN 
        DependencyHierarchy eh ON e.dependProcessName = eh.processName
)
SELECT *
FROM DependencyHierarchy
ORDER BY HierarchyLevel, processName, dependProcessName;
GO

它会引发此错误：

解析查询时出错。 [令牌行号= 1，令牌
行偏移量= 1，令牌错误= WITH]

There was an error parsing the query. [ Token line number = 1,Token line offset = 1,Token in error = WITH ]

表具有此数据：

    processName dependProcessName
    P1          P2
    P2          P3
    P3          P4
    P4          P5
    P6          P7

推荐答案

与
common_table_expression ：

列名

在公用表表达式中指定列名。不允许在单个CTE定义中使用重复的名称。 指定的
列名的数量必须与CTE_query_definition的结果
集中的列数匹配。

Specifies a column name in the common table expression. Duplicate names within a single CTE definition are not allowed. The number of column names specified must match the number of columns in the result set of the CTE_query_definition.

只有在
查询定义中提供了所有结果列的唯一名称时，列名列表才是可选的
。

将列 HierarchyLevel 添加到 cte 列列表中：

WITH DependencyHierarchy(processName,dependProcessName, HierarchyLevel)
AS
(
   ...
)

LiveDemo

或将其保留为空（列名将从第一个 SELECT ）：

or just leave it empty (column names will be derived from first SELECT):

WITH DependencyHierarchy AS
(
   ...
)

LiveDemo2

这篇关于CTE查询SQL Server中的解析错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！