这看起来很荒谬,但是我无法正确解决-请多多帮助!
基本上:我正在使用RMySQL做一些简单的SQL,以使我了解SQL的工作原理。作为一个简单的示例,我想将一些SQL select
查询链接在一起。 RMySQL PDF中对此进行了介绍-但其中的示例似乎是不正确的语法(http://cran.r-project.org/web/packages/RMySQL/RMySQL.pdf,第3页,示例6)。
如果我有三个查询,请这样说:
q1 <- "SELECT db.table FROM table WHERE stuff = 'blah' "
q2 <- "SELECT db.other_table FROM other_table WHERE stuff = 'different blah' "
q3 <- "SELECT db.table2 FROM table2 WHERE table2 = 1000"
并尝试按以下步骤
paste
进行操作:script <- paste(q1, q2, q3, sep=";")
结果是
> script
[1] "SELECT db.table FROM table WHERE stuff = 'blah' ;SELECT fb.other_table FROM
other_table WHERE stuff = 'different blah' ;SELECT db.table2 FROM table2 WHERE table2 =
'1000'
因此调用
dbSendQuery
显然会失败。我已经尝试过
\"
,但这也行不通:q1 <- "SELECT db.table FROM table WHERE stuff = 'blah' \" "
q2 <- "SELECT db.other_table FROM other_table WHERE stuff = 'different blah' \""
q3 <- "SELECT db.table2 FROM table2 WHERE table2 = 1000 \" "
script <- paste(q1, q2, q3, sep=";")
> script
[1] "SELECT db.table FROM table WHERE stuff = 'blah' \" ; ;SELECT db.other_table FROM
other_table WHERE stuff = 'different blah' \";SELECT db.table2 FROM table2 WHERE table2
= 1000 \" "
谁能指出我做错了吗?
编辑:只是为了澄清,通过RMySQL执行此操作,如下所示:
my.queries <- dbGetQuery(my.con, script, client.flag = CLIENT_MULTI_STATEMENTS)
根据RMySQL手册,我得到
RS-DBI driver: (could not run statement: You have an error in your SQL syntax;
大概是因为
paste
函数的结果应该是:"SELECT db.table FROM table WHERE stuff = 'blah'" ;"SELECT fb.other_table FROM
other_table WHERE stuff = 'different blah'" ;"SELECT db.table2 FROM table2 WHERE table2
= '1000'"
每个单独的查询都可以正常工作,因此我假设是导致问题的原因是我的
paste
命令。编辑:简化此操作:假设我有两个字符串,如下所示:
t1 <- "the 'stuff'"
t2 <- "more 'stuff'"
paste(t1, t2, sep=";")
[1] "the 'stuff' ; more 'stuff' "
我想要的是粘贴命令的结果为
"the 'stuff'";"more 'stuff'"
。 最佳答案
您必须将参数client.flag = CLIENT_MULTI_STATEMENTS
传递给函数dbConnection
,而不是传递给dgGetQuery
。
然后,您的第一种方法应该起作用:
q1 <- "SELECT db.table FROM table WHERE stuff = 'blah' "
q2 <- "SELECT db.other_table FROM other_table WHERE stuff = 'different blah' "
q3 <- "SELECT db.table2 FROM table2 WHERE table2 = 1000"
script <- paste(q1, q2, q3, sep=";")
关于mysql - 在R粘贴函数中混合单引号和双引号?,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/21368771/