的R - R中

Question

在该R MySQL查询内部数据帧值 - 是否有可能使一个IN，其中，在值来自数据帧列MySQL查询？

实例的我想要做的事：

Directory = read.csv("worlddirectory.csv", header = TRUE, sep = ",",stringsAsFactors=FALSE) 

Active_Customers = Directory[(Directory$Status == "Active"),] 

PhoneNumbers = dbGetQuery(DBConnection, 
" 
Select 
db.phonenumbers, 
db.names 
from 
database db 
where 
db.country IN 
(
Active_Customers$Country 
);" 

Answer 1

我们可以看到here，预期的声明是这样的：

WHERE column_name IN (value1, value2, ...); 

我们可以使用paste的说法collapse=", "获得所需的格式。我想，这应该工作：

PhoneNumbers = dbGetQuery(DBConnection, 
paste0("SELECT db.phonenumbers, db.names ", 
"FROM database db ", 
"WHERE db.country IN (", 
     paste(Active_Customers$Country,collapse=", "),");")) 

例子：

Active_Customers <- data.frame(Country=c("NL","BE","US")) 

paste0("SELECT db.phonenumbers, db.names ", 
"FROM database db ", 
"WHERE db.country IN (", 
paste(Active_Customers$Country,collapse=", "),");") 

输出：

[1] "SELECT db.phonenumbers, db.names FROM database db WHERE db.country IN (NL, BE, US);" 

希望这有助于！