使用 AS 从 CTE 选择？ SQL Server 2008

Question

我正在尝试将 PostgreSQL 转换为 SQL Server。但这个查询不起作用。

我做错了什么？我尝试在WITH之前添加分号，但没有成功。

   SELECT 
      member_a AS you, member_b AS mightknow, shared_connection,
      CASE
         WHEN (n1.member_job_country = n2.member_job_country AND n1.member_job_country = n3.member_job_country) THEN 'country in common'
         WHEN (n1.member_unvan_id = n2.member_unvan_id AND n1.member_unvan_id = n3.member_unvan_id) THEN 'unvan in common'
         ELSE 'nothing in common'
      END AS reason
   FROM (
      WITH transitive_closure(member_a, member_b, distance, path_string, direct_connection) AS
        (SELECT 
             member_a, member_b, 1 AS distance,
             CAST(member_a as varchar(MAX)) + '.' + CAST(member_b as varchar(MAX)) + '.' AS path_string,
             member_b AS direct_connection
         FROM Member_Contact_Edges
         WHERE member_a = 45046 -- set the starting node

         UNION ALL

         SELECT 
             tc.member_a, e.member_b, tc.distance + 1,
             CAST(tc.path_string as varchar(MAX)) + CAST(e.member_b as varchar(MAX)) + '.' AS path_string,
             tc.direct_connection
         FROM Member_Contact_Edges AS e
         JOIN transitive_closure AS tc ON e.member_a = tc.member_b
         WHERE tc.path_string NOT LIKE '%' + CAST(e.member_b as varchar(MAX)) + '.%'
         AND tc.distance < 2
        )

   SELECT
       member_a, member_b,direct_connection AS shared_connection
   FROM transitive_closure
   WHERE distance = 2
  ) AS youmightknow
  LEFT JOIN Members AS n1 ON youmightknow.member_a = n1.memberID
  LEFT JOIN Members AS n2 ON youmightknow.member_b = n2.memberID
  LEFT JOIN Members AS n3 ON youmightknow.shared_connection = n3.memberID
  WHERE (n1.member_job_country = n2.member_job_country 
         AND n1.member_job_country = n3.member_job_country)
        OR (n1.member_unvan_id = n2.member_unvan_id 
            AND n1.member_unvan_id = n3.member_unvan_id);

我得到的错误：

消息 156，级别 15，状态 1，第 11 行
关键字“WITH”附近的语法不正确。
消息 319，15 级，状态 1，第 11 行
关键字“with”附近的语法不正确。如果这个语句是一个 公用表表达式， xmlnamespaces 子句或更改 跟踪上下文子句，前一个 语句必须以 分号。
消息 102，第 15 级，状态 1，第 34 行
“)”附近的语法不正确。

这是参考； 数据库中的图表 - SQL遇见社交网络 - 查看文章底部的 Facebook 建议部分。

提前致谢

Answer 1

CTE 声明需要位于顶部。您还可以使用逗号声明和连接多个 CTE，而不是混合 CTE 和派生表。

尝试

;WITH 
/*First CTE declaration*/
transitive_closure(member_a, member_b, distance, path_string, direct_connection) 
AS
(   
     ...
),
/*Second CTE declaration*/
youmightknow AS
(
SELECT member_a, member_b,direct_connection AS shared_connection
FROM transitive_closure
WHERE distance = 2
)        
SELECT member_a AS you,
...
FROM youmightknow

Answer 2

将 CTE 定义移至 SQL 语句的开头。关键字 WITH 在语句开头出现一次，用于引入一个或多个 CTE，然后可以在以下 SQL 中引用这些 CTE。看看这个 CTE 示例，它将为你清理干净。