如何使用glue_sql()在动态SELECT语句中避免使用paste类拼接函数

方案1：使用glue自带的矢量化+折叠功能（完全替代paste0） #

glue本身支持向量输入，搭配glue_collapse()就可以完全替代paste0完成动态字段拼接，同时还能保留glue_sql的安全转义特性，避免SQL注入风险。
修改后的完整代码如下：

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

# 示例数据库（和原有逻辑一致）
set.seed(1)
ps <- data.frame(plot = rep(1:3, each = 4),
                 spp = rep(1:3*10, 2),
                 wtX = rnorm(12, 10, 2) %>% round(1))
con <- dbConnect(RSQLite::SQLite(), "")
dbWriteTable(con, "ps", ps)

# 目标物种
our_spp <- c(10, 20)

# 1. 矢量化生成每个物种的聚合逻辑，无需paste0
agg_fields <- glue("SUM(CASE WHEN ps.spp = {spp} THEN ps.wtX END) AS s{spp}_wtX",
                   spp = our_spp) %>% 
  glue_collapse(sep = ",\n")

# 2. 拼接主查询
sq <- glue_sql("
  SELECT ps.plot,
         {agg_fields}
  FROM ps
  WHERE ps.spp IN ({our_spp*})
  GROUP BY ps.plot
", .con = con)

# 查询结果和原有逻辑完全一致
dbGetQuery(con, sq)

运行结果和你提供的示例完全一致：

plot s10_wtX s20_wtX
1    1    21.9    10.4
2    2    11.0    22.2
3    3     9.4    13.0

方案2：使用dbplyr直接用dplyr语法操作数据库（无需手写SQL） #

如果你习惯dplyr的语法，可以直接用dbplyr操作数据库表，连SQL都不需要手动编写，动态字段会自动生成，适配任意数量的目标物种：

library(dbplyr)

tbl(con, "ps") %>% 
  filter(spp %in% our_spp) %>% 
  group_by(plot, spp) %>% 
  summarise(wtX_sum = sum(wtX), .groups = "drop") %>% 
  pivot_wider(
    id_cols = plot,
    names_from = spp,
    values_from = wtX_sum,
    names_prefix = "s",
    names_suffix = "_wtX"
  ) %>% 
  collect()

这个方案的优势是完全不需要考虑SQL拼接逻辑，所有语法都是R原生的dplyr语法，可读性和可维护性更高。

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