将另一个变量粘贴到我在 R 中的 SQL 查询中

Question

所以我在R中使用这三个包来连接到一些数据库并查询它们：

library(rJava)
library(RJDBC)
library (RPostgreSQL)
id_query <- dbGetQuery(conn2, "SELECT b.id id FROM table1 a LEFT JOIN table2 b ON a.id = b.id WHERE a.id = 1684 AND b.id <> 40378;")

这将产生如下输出：

id
25559
30352
15352
17587
16480
16296
40449
34962
25827
37282

但是我想获取这些结果并将它们粘贴到使用IN的WHERE子句中

results_query <- dbGetQuery(con, "SELECT
i.event_date,
i.id,
i.id2,
i.id3,
i.id4,
i.id5,
COUNT(i.sales) sales,
COUNT(c.volume) volume
FROM table1 i
LEFT JOIN
table2 c
ON i.id = c.id
AND i.id2 = c.id2
AND i.id3 = c.id3
WHERE i.event_date = DATE('2018-06-18')
AND i.id IN (**RESULTS FROM id_query**)
GROUP BY 1,2,3,4,5,6
LIMIT 10
;")

所以我希望这一行读成这样：

AND i.id IN (25559, 30352, 15352, 17587, 16480, 16296, 40449, 34962, 25827, 37282)

我试过这样做：

results_query <- dbGetQuery(con, "SELECT
i.event_date,
i.id,
i.id2,
i.id3,
i.id4,
i.id5,
COUNT(i.sales) sales,
COUNT(c.volume) volume
FROM table1 i
LEFT JOIN
table2 c
ON i.id = c.id
AND i.id2 = c.id2
AND i.id3 = c.id3
WHERE i.event_date = DATE('2018-06-18')
AND i.id IN (", paste(id_query$id, collapse = ", "), ")
GROUP BY 1,2,3,4,5,6
LIMIT 10
;")

这是我从这个答案中得到的语法，但它会产生此错误：

Error in .verify.JDBC.result(s, "Unable to execute JDBC prepared statement ",  : 
Unable to execute JDBC prepared statement SELECT
i.event_date,
i.id,
i.id2,
i.id3,
i.id4,
i.id5,
COUNT(i.sales) sales,
COUNT(c.volume) volume
FROM table1 i
LEFT JOIN
table2 c
ON i.id = c.id
AND i.id2 = c.id2
AND i.id3 = c.id3
WHERE i.event_date = DATE('2018-06-18')
AND i.id IN  ( (Method Connection.prepareStatement is not yet implemented)

任何人都有：

A.(修复当前查询的解决方案或

B.(另一种选择？

Answer 1

尝试在原始查询中放置一个 paste(...， sep = ''(：

results_query <- dbGetQuery(con,  paste("SELECT 
i.event_date,
i.id,
i.id2,
i.id3,
i.id4,
i.id5,
COUNT(i.sales) sales,
COUNT(c.volume) volume
FROM table1 i
LEFT JOIN
table2 c
ON i.id = c.id
AND i.id2 = c.id2
AND i.id3 = c.id3
WHERE i.event_date = DATE('2018-06-18')
AND i.id IN (", paste(id_query$id, collapse = ", "), ")
GROUP BY 1,2,3,4,5,6
LIMIT 10
;", sep = ''))