sql - sql where语句中的r列值

Question

我有一个数据集，我正在尝试将特定列的内容传递到 SQL where 语句中。

例如，假设 iris 是我的数据集

       data(iris)
       head(iris)

       Sepal.Length Sepal.Width Petal.Length Petal.Width Species
       5.1         3.5          1.4         0.2  setosa
       4.9         3.0          1.4         0.2  setosa
       4.7         3.2          1.3         0.2  setosa
       4.6         3.1          1.5         0.2  setosa
       5.0         3.6          1.4         0.2  setosa
       5.4         3.9          1.7         0.4  setosa

我想将列 Species { setosa, setosa, setosa.....setosa} 的内容传递给我的 sql 查询 where 语句

sqlQuery(abcd, paste("Select * from TestTableName1 
                       where WHERE DESCRIPTION 
                          IN (values of Species column from r dataframe)");

这里需要帮助

Answer 1

你的问题其实是关于字符串操作的(这是偶然的，你的字符串最终会传递给sqldf)，答案是你把它粘贴在一起，或者使用sprintf 如果你喜欢:

vals = paste(paste0('"', levels(iris$Species), '"'), collapse = ", ")
vals
## [1] "\"setosa\", \"versicolor\", \"virginica\""

vals.paren = paste0("(", vals, ")")

qry = paste("select * from table where description in ", vals.paren)
qry
## [1] "select * from table where description in  (setosa, versicolor, virginica)"

# sprintf makes the bounding parentheses cleaner

qry = sprintf("select * from table where description in (%s)", vals)
qry
## [1] "select * from table where description in (setosa, versicolor, virginica)"