mysql - rsqlite_send_query(conn @ ptr，statement)中的错误:“(”附近:语法错误_____

当我不得不将行转换成列时，出现了这个错误

library(sqldf)
sqldf("SELECT id,name,ROW_NUMBER() over (PARTITION BY id order by id) as row_no FROM dd")

rsqlite_send_query中的错误（conn@ptr，语句）：靠近“（”：语法
错误_____
我应该怎么做，或者请给我一个选择，因为我想在shiny应用程序中使用它

最佳答案

假设我们有这个：

library(sqldf)
dd <- data.frame(id = c(1, 2, 3), name = c("A", "B", "C"), X = 1:9)

然后这三个都给出了相同的结果：

# 1. PostgreSQL - supports over/partition

library(RPostgreSQL)
sqldf("SELECT id, name, \"X\", ROW_NUMBER() over (PARTITION BY id order by id) as row_no
       FROM dd order by id")

# 2. SQLite - does not support over/partition, use join instead

# Ensure RPostgreSQL is NOT loaded to force use of SQLite.
sqldf("select a.*, count(*) row_no
       from dd a join dd b on a.id = b.id and b.rowid <= a.rowid
       group by a.rowid
       order by a.rowid")

# 3. Base R

transform(dd, row_no = ave(1:nrow(dd), id, FUN = seq_along))

关于mysql - rsqlite_send_query(conn @ ptr，statement)中的错误:“(”附近:语法错误_____，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/49661849/