英文:
Avoid duplicates columns with left join sqldf
问题
我尝试在R中使用sqldf包进行左连接。
data <- sqldf(
"SELECT a.*, b.var1, b.var2, b.id
FROM table1 as a LEFT JOIN table2 as b
ON a.id=b.id "
)
不幸的是，我在data中得到了两列名为"id"的列。我该如何避免这个问题？
谢谢
Chloé
英文:
I'm trying to do a left join using sqldf package on R.
data <- sqldf(
" SELECT a.*, b.var1, b.var2, b.id
FROM table1 as a LEFT JOIN table2 as b
ON a.id=b.id "
)
Unfortunately I get two columns named "id" in data. How can I avoid this problem?
Thanks
Chloé
答案1
得分: 1
以下是代码的翻译部分：
请注意，代码要求两个id，a.* 包括所有 a 列，同时明确要求 b.id。你可以简单地去掉 b.id。
或者，如果你想要所有 b 列，除了id之外，可以使用 USING 而不是 ON。
如果你确实想要两个id，只是命名不同，那么这将把 b.id 命名为 id2。
英文:
Note that the code is asking for two id's as a.* includes all a columns and it explicitly asked for b.id. You can simply drop the b.id
library(sqldf)
# test inputs
table1 <- data.frame(id = 0, a = 3)
table2 <- data.frame(id = 0, var1 = 1, var2 = 2)
sqldf(
" SELECT a.*, b.var1, b.var2
FROM table1 as a LEFT JOIN table2 as b
ON a.id = b.id "
)
## id a var1 var2
## 1 0 3 1 2
or if you want all b columns except id then use USING instead of ON
sqldf(
" SELECT *
FROM table1 as a LEFT JOIN table2 as b
USING(id) "
)
## id a var1 var2
## 1 0 3 1 2
If you do want two id's but just named differently then this will name the b.id as id2.
sqldf(
" SELECT a.*, b.var1, b.var2, b.id as id2
FROM table1 as a LEFT JOIN table2 as b
ON a.id=b.id "
)
## id a var1 var2 id2
## 1 0 3 1 2 0
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论