如何在其中键是key2的子字符串的MySQL中进行联接? [英] HOw to make a join in MySQL where the key is a substring of the key2?

问题描述

我必须要连接的表(后来是左外部连接) 在字段refs.isbn中，更多的isbns也是正确的.在pdfrefs.isbn字段中，isbn只是一个右.因此:pdfrefs.isbn是refs.isbn的子字符串.这里的任何人都可以帮助找到我的错误吗?

I have to tables, which I want to join (later an left outer join) in the field refs.isbn are more isbns but also the right one. in the field pdfrefs.isbn is only one right one isbn. So: pdfrefs.isbn is a substring of refs.isbn. Can anyone here help to find my mistake?

谢谢 沃尔特

我尝试了以下操作:

SELECT
    dfrefs.title,
    pdfrefs.isbn,
    REGEXP_SUBSTR(refs.isbn,pdfrefs.isbn),
    refs.url,
    refs.isbn,
    pdfrefs.link_to_pdf
FROM refs
    JOIN pdfrefs
WHERE
    REGEXP_SUBSTR(refs.isbn,pdfrefs.isbn) = pdfrefs.isbn;

但是结果不是我希望的. pdfrefs中的记录数是830，refs中的记录数是918.我在结果中得到了2000多个记录.正确的联接可能少于918条记录.

But the result is not that I would like to expect. Number of Records in pdfrefs is 830 and in refs is 918. I get more then 2000 records in the result. The right join might be less than 918 records.

推荐答案

如果refs.isbn是pdfrefs.isbn的substr，则可以使用简单的like来加入

If refs.isbn is substr of pdfrefs.isbn you could use a simple like for join

SELECT
    dfrefs.title,
    pdfrefs.isbn,
    REGEXP_SUBSTR(refs.isbn,pdfrefs.isbn),
    refs.url,
    refs.isbn,
    pdfrefs.link_to_pdf
FROM refs
JOIN pdfrefs ON  pdfrefs.isbn like concat('%',refs.isbn, '%');

这篇关于如何在其中键是key2的子字符串的MySQL中进行联接?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！