像sqldf上的内部联接 [英] Inner join on LIKE sqldf

问题描述

如何在R中使用sqldf将 LIKE 子句与内部联接一起使用?

How can I use the LIKE clause with an inner join using sqldf in R?

代码:

Name <- c("Jack","Jill","Romeo")
Name <- as.data.frame(Name)
FullName <- c("School Jack H", "School Juliet G", "College Jill M", "College Romeo F")
Marks <- c("100","82","54","0")
FullBio <- cbind(FullName, Marks)
FullBio <-as.data.frame(FullBio)

然后当我运行时:

sqldf("select a.*, b.* from Name a join FullBio b on a.Name like '%'+b.[FullName]+'%'") 

返回0行.

为什么?请问我还有其他选择吗?很抱歉让您创建这么多变量来运行我的代码.

Why? What are my other alternatives please. I apologise for making you create so many variables to run my code.

推荐答案

字符串连接运算符在SQLite中为 || :

The string concatenation operator is || in SQLite:

sqldf("select * from Name join FullBio on FullName like '%' || Name || '%'")

给予:

   Name        FullName Marks
1  Jack   School Jack H   100
2  Jill  College Jill M    54
3 Romeo College Romeo F     0

其中任何一个也可以工作:

Any of these would also work:

sqldf("select * from Name join FullBio on instr(FullName, Name)")

sqldf("select * from Name join FullBio on like('%' || Name || '%', FullName)")

这篇关于像sqldf上的内部联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！