R语言DBI查询无法识别变量存储的筛选条件如何解决

服务端基于R的DBI接口连接SQLite创建测试表：

library(dplyr)
library(DBI)

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

iris$id = 1:nrow(iris)
dbWriteTable(con, "iris", iris)

生成带重复值的待筛选行号R变量：

rows_to_select = sample.int(10, 5, replace = TRUE)
# 示例取值：1 1 8 8 7

直接在SQL语句中引用R变量会报找不到列的错误：

DBI::dbGetQuery(con, "select a.* from (select *, row_number() over (order by id) as rnum from iris)a where a.rnum in (rows_to_select) limit 100;")
# 报错：Error: no such column: rows_to_select

硬编码行号到IN子句虽然能运行，但存在两个核心缺陷：

• 无法动态适配R变量的取值变化，每次变量更新都要手动改SQL字符串
• IN子句会自动对匹配值去重，重复传入的行号只会返回一次结果，例如传入(1,5,5)不会返回两次第5行，不符合需求

核心思路两点：不要用字符串拼接硬编码SQL值，用内连接代替IN子句保留重复匹配结果，以下是所有DBI兼容数据库通用的实现方式：

1. 先将待筛选的行号转换为带排序标识的数据集，写入数据库临时表（临时表会在连接断开时自动删除，不会残留垃圾数据）

# 构造匹配数据集，seq列用于保证返回结果顺序和原始选择顺序一致
select_df <- data.frame(
  rnum = rows_to_select,
  seq = seq_along(rows_to_select)
)
# 写入临时表
dbWriteTable(
  con, 
  name = "tmp_sel_rows", 
  value = select_df, 
  temporary = TRUE, 
  overwrite = TRUE
)

2. 用内连接关联原表和临时表取数，自动保留重复行

result <- dbGetQuery(con, "
WITH iris_with_rnum AS (
  SELECT *, row_number() OVER (ORDER BY id) AS rnum FROM iris
)
SELECT t.*
FROM iris_with_rnum t
INNER JOIN tmp_sel_rows s
  ON t.rnum = s.rnum
ORDER BY s.seq
LIMIT 100
")

• 动态适配：R变量rows_to_select更新后，只需要重新写入临时表即可，不需要修改SQL语句逻辑
• 保留重复结果：IN子句的逻辑是「判断值是否存在于集合中」，会自动去重；而内连接是逐行匹配关联关系，临时表里有多少个重复行号，最终就会返回多少条对应的数据行，完全匹配带重复值的抽样需求
• 顺序一致：通过seq字段排序，返回结果的行顺序和rows_to_select里的行号顺序完全一致，不会出现乱序
• 安全合规：没有用字符串拼接的方式往SQL里塞值，从根源上避免了SQL注入风险，也不需要手动处理特殊值转义问题

如果使用的数据库驱动支持参数化传递数据框（例如最新版RSQLite），也可以省略写临时表的步骤，直接在dbGetQuery的params参数里传入select_df，逻辑完全一致。

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