如何基于R数据框中的字段对查询SQL Server表的匹配记录

原写法问题说明 #

当前的IN查询是分别校验两个字段的存在性，会匹配到person_id在列表、job_id也在列表但不属于同一条记录的结果，无法满足配对校验需求，以下是三种可行实现方式：

方案1：行值匹配语法（适合小批量校验，兼容多数SQL Server版本） #

通过拼接配对条件的方式实现精准匹配，SQL会生成(person_id = 1 AND job_id = 100) OR (person_id = 2 AND job_id = 101)这类逻辑：

library(glue)
library(DBI)

data_to_check <- data.frame(person_id = c(1, 2, 3, 4),
                           job_id = c(100, 101, 102, 102))

# 构造每一行的配对匹配条件
pair_conditions <- lapply(1:nrow(data_to_check), function(i) {
  glue_sql("(person_id = {data_to_check$person_id[i]} AND job_id = {data_to_check$job_id[i]})", .con = x)
})
# 拼接所有条件
conditions_collapsed <- glue_collapse(pair_conditions, sep = " OR ")

# 执行查询
comparison <- dbGetQuery(conn = x,
           glue_sql(.con = x,
                    "SELECT * FROM [TABLE]
                    WHERE {conditions_collapsed}"))

如果你的SQL Server版本在2008及以上，也可以简化为行值IN写法，把WHERE子句替换为WHERE (person_id, job_id) IN ({pairs_collapsed})即可。

方案2：VALUES子句关联查询（适合中批量校验，性能更稳定） #

将待校验的配对拼接为VALUES临时结果集，和原表做关联查询，性能优于长OR/长IN语句，尤其当原表有(person_id, job_id)联合索引时效率更高：

# 构造VALUES的每一行数据
value_rows <- lapply(1:nrow(data_to_check), function(i) {
  glue_sql("({data_to_check$person_id[i]}, {data_to_check$job_id[i]})", .con = x)
})
values_collapsed <- glue_collapse(value_rows, sep = ", ")

# 关联查询
comparison <- dbGetQuery(conn = x,
           glue_sql(.con = x,
                    "SELECT t.* FROM [TABLE] t
                    INNER JOIN (VALUES {values_collapsed}) AS check_list(person_id, job_id)
                    ON t.person_id = check_list.person_id AND t.job_id = check_list.job_id"))

方案3：临时表关联（适合万条以上大批量校验） #

如果待校验的配对数量非常大，拼接SQL会有长度限制，此时可以先将待校验数据写入临时表再关联：

# 写入SQL Server临时表
dbWriteTable(x, "#check_list", data_to_check, temporary = TRUE)

# 关联查询
comparison <- dbGetQuery(x,
           "SELECT t.* FROM [TABLE] t
           INNER JOIN #check_list c ON t.person_id = c.person_id AND t.job_id = c.job_id")

# 可选：用完删除临时表释放资源
dbExecute(x, "DROP TABLE #check_list")

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