使用多个CTE [英] Use Multiple CTE

问题描述

无法弄清楚如何使用多个CTE

Cannot figure out how to use multiple CTE

操作失败

; with [cteOne] as (
  select 1 as col
),
  [cteTwo]  as (
  select 2 as col
)
select 'yesA' where exists (select * from [cteOne])
select 'yexB' where exists (select * from [cteTwo])

这有效-但这不是我所需要的

This works - but this is not what I need

; with [cteOne] as (
  select 1 as col
),
  [cteTwo]  as (
  select 2 as col
)
select * from [cteOne]
union 
select * from [cteTwo]

真正的语法是联接到row_number（）分区

我刚结束使用派生表

The real syntax was a join to row_number() partition
I just ended up using a derived table

推荐答案

第一个失败，因为一个CTE或一组CTE只能跟一个语句。

The first one fails because a CTE or set of CTEs can only be followed by a single statement.

您可以将其重写为

; with [cteOne] as (
  select 1 as col
)
select 'yesA' where exists (select * from [cteOne])

; with [cteTwo]  as (
  select 2 as col
)
select 'yexB' where exists (select * from [cteTwo])

这篇关于使用多个CTE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！