如何用dplyr语法编写内部查询引用不同表的子查询？

要实现你需要的「从table2筛选出table1中特定日期对应的ID数据」，正确的dplyr写法是保留子查询的数据库表对象（tbl），不将数据拉取到本地，让dplyr自动生成数据库端执行的嵌套SQL，避免大量ID导致的数据库报错。

正确代码实现 #

library(DBI)
library(dplyr)

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

# 初始化表数据（和你的示例一致）
table1 <- data.frame(
  id = c(1,2,3),
  date = c("2019-01-04", "2019-01-04", "2019-01-05")
)

table2 <- data.frame(
  id = c(1,1,2,3),
  date = c("AAA", "BBB", "CCC", "DDD")
)

dbWriteTable(con, "table1", table1, overwrite = T)
dbWriteTable(con, "table2", table2, overwrite = T)

# 核心查询代码
result <- con %>%
  tbl("table2") %>%
  filter(
    id %in% (
      tbl(con, "table1") %>%
        filter(date == "2019-01-04") %>%
        select(id)  # 用select保留tbl对象，而非pull拉取本地数据
    )
  )

# 查看生成的SQL（和你目标的原生SQL一致）
result %>% show_query()

# 获取最终结果
result %>% collect()

生成的SQL验证 #

执行show_query()后，会得到和你目标原生SQL完全一致的语句：

SELECT *
FROM `table2`
WHERE (`id` IN (SELECT `id` FROM `table1` WHERE (`date` = '2019-01-04')))

错误方案问题分析 #

• 方案1：pull(id)将符合条件的ID拉取到本地R环境，生成的SQL会把所有ID硬编码到IN列表中。当ID数量达数十万时，会超出MS SQL Server等数据库的参数长度限制，触发报错。
• 方案2：嵌套子查询中使用pull(id)同样会触发本地数据拉取，且直接在嵌套里调用con %>% tbl(...)会导致dplyr无法正确解析数据库连接对象，进而抛出no applicable method for 'escape'错误。

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