R语言操作SQL数据库时如何正确定义与调用CTE公用表表达式

问题核心原因 #

你写的CTE无法运行本质是对CTE的生命周期理解有偏差：
CTE是单条SQL内部的临时逻辑，必须和引用它的查询语句写在同一段SQL里，不能单独执行WITH子句“定义”CTE后，再在另一次独立的数据库调用里引用它。你之前单独执行只有with cte_setosa as (...)的代码，相当于给数据库传了半句话，自然会报错。
另外你最后关联查询的SQL里还有个小bug：查询作用域里没有名为iris的表对象，你要取CTE里的setosa数据，应该选别名base下的字段。 #

环境初始化 #

library(dplyr) 
library(DBI) 
library(glue)

con <- dbConnect(RSQLite::SQLite(), ":memory:") 
dbWriteTable(con, "iris", iris) 

方法1：单次查询内联CTE（最通用写法） #

把CTE定义和后续关联逻辑拼成完整的单条SQL，一次性传给数据库执行即可：

# 生成采样行号参数
setosa_sample_vector <- glue_sql(
  paste0("(", paste(sample(1:50, 30, replace = T), collapse = "),("), ")"), 
  .con = con
)

# 完整SQL：WITH子句定义CTE后直接写关联查询逻辑
full_query <- "
WITH cte_setosa AS (
  SELECT *, row_number() OVER (ORDER BY Species) as rn
  FROM iris
  WHERE Species = 'setosa'
)
SELECT base.*
FROM (VALUES ?setosa_sample_vector) sv
LEFT JOIN cte_setosa as base ON base.rn = sv.column1
"

# 执行查询拿到结果
res <- dbGetQuery(con, full_query, params = list(setosa_sample_vector = setosa_sample_vector))

方法2：创建视图实现CTE逻辑复用 #

如果你需要在多次查询里反复用到cte_setosa的逻辑，没必要每次都拼WITH子句，直接在库里创建视图即可，一次创建后所有查询都能直接调用：

# 一次创建视图，逻辑和你要的CTE完全一致
dbExecute(con, "
CREATE VIEW cte_setosa AS
SELECT *, row_number() OVER (ORDER BY Species) as rn
FROM iris
WHERE Species = 'setosa'
")

# 后续查询直接调用视图，不需要重复写CTE定义
res2 <- dbGetQuery(con, "
SELECT base.*
FROM (VALUES ?setosa_sample_vector) sv
LEFT JOIN cte_setosa as base ON base.rn = sv.column1
", params = list(setosa_sample_vector = setosa_sample_vector))

内容的提问来源于stack exchange，提问作者stats_noob