sql - 通过前缀连接，类似于 SQL 的 ON ... LIKE

Question

如何使用列的前缀(不同长度)作为键来执行连接/合并？我正在尝试翻译以下 SQL 代码:

SELECT a.person_id, a.tn_code, b.list_id
FROM tblA a
INNER JOIN tblB b
ON tn_code LIKE TnCode + "%"

未决

person_id  tn_code
        1    C18.4
        2  M8820/9
        3     X20.
...

结语

ListID TnCode
  1.01   A0.1
  1.01   A0.2
...

我有想法比如准备一个新的key TnCode_prefix = gsub("^(.*)\\.(.*)$", "\\1", TnCode) 然后加入在新列上，或使用 data.table 的滚动连接，但它们只是近似翻译？ R 中有完全等价的东西吗？

我知道使用 sqldf 并将原始 SQL 语句简单地传递给 sqldf，但我想知道是否还有其他方法。

Answer 1

动态创建前缀并加入该前缀怎么样？我使用 dplyr 创建前缀并进行连接。

library(dplyr)

# Fake Data
set.seed(1093)
tblA = data.frame(person_id=sample(1:10, 50, replace=TRUE),
                  tn_code = paste0(sample(paste0(paste0(rep(LETTERS[1:3],3),c(40:42,401:403,421:423))), 50, replace=TRUE),
                                   ".", sample(160:170, 50, replace=TRUE)))
tblB = data.frame(ListID=paste0(sample(1:10, 50, replace=TRUE),".",
                                sample(10:20, 50, replace=TRUE)),
                  TnCode = paste0(sample(paste0(paste0(rep(LETTERS[1:3],3),c(40:42,401:403,421:423))), 50, replace=TRUE),
                                  ".", sample(160:170, 50, replace=TRUE)))

# Join on first letter of tn_code and TnCode
newTbl = tblA %>% mutate(join_prefix=gsub("(.*)\\..*", "\\1", tn_code)) %>%
  left_join(tblB %>% mutate(join_prefix=gsub("(.*)\\..*", "\\1", TnCode)),
            by="join_prefix")