跨 2 个表查找部分字符串匹配的 SQL 查询 [英] SQL query to find partial string match across 2 tables

问题描述

我正在尝试查找列地址(用户表)中与地址(address_effect 表)匹配的任何项目.我正在使用 XAMPP(使用 MariaDB)在我的本地系统上对此进行测试

I'm trying to find any items in column address (users table) that have a match in Address (address_effect table). I'm testing this on my local system with XAMPP (using MariaDB)

+------------------+-----------------+------------------+--------------------------+
|        ID        |    firstname    |  lastname        |    address               |
|                  |                 |                  |                          |
+----------------------------------------------------------------------------------+
|     1            |    john         |    doe           |james street, idaho, usa  |                    
|                  |                 |                  |                          |
+----------------------------------------------------------------------------------+
|     2            |    cindy        |   smith          |rollingwood av,lyn, canada|
|                  |                 |                  |                          |
+----------------------------------------------------------------------------------+
|     3            |    rita         |   chatsworth     |arajo ct, fremont, cali   |
|                  |                 |                  |                          |
+------------------+-----------------+---------------------+-----------------------+
|     4            |    randy        |   plies          |smith spring, lima, peru  |                       
|                  |                 |                  |                          |
+----------------------------------------------------------------------------------+
|     5            |    Matt         |   gwalio         |park lane, atlanta, usa   |
|                  |                 |                  |                          |
+------------------+-----------------+------------------+--------------------------+

address_effect表

+---------+----------------+
|idaho    |potato, tater   |
+--------------------------+
|canada   |cold, tundra    |
+--------------------------+
|fremont  | crowded        |
+--------------------------+
|peru     |alpaca          |
+--------------------------+
|atlanta  |peach, cnn      |
+--------------------------+
|usa      |big, hard       |
+--------+-----------------+

我已经尝试使用带有 LIKE 的内连接来查找匹配的字符串.

I've tried using inner join with LIKE to find matching string.

如果我使用这个查询，它不会找到任何项目:

If I use this query it doesn't find any items:

SELECT users.firstname, users.lastname, users.address
FROM users
INNER JOIN db_name.address_effect
ON
(address_effect.Address LIKE '%' + users.address + '%'
OR users.address LIKE '%' || address_effect.Address || '%')

然后我尝试了以下查询，它列出了用户表中的所有项目，而不是仅列出那些在 address_effect 中匹配的项目

Then I tried the following query, it lists all the items from user table, instead of only those items that have a match in address_effect

SELECT DISTINCT users.firstname, users.lastname, users.address
FROM users
INNER JOIN db_name.address_effect
ON
(address_effect.Address LIKE '%' || users.address || '%'
OR users.address LIKE '%' || address_effect.Address || '%')

我在这里错过了什么?

谢谢.

推荐答案

据我了解您的示例数据，您希望将 users 地址的一部分与另一个表中的值进行匹配.

As I understand your sample data, you want to match parts of users addresses against values in the other table.

您可能想尝试 find_in_set().LIKE 匹配更准确，因为它只匹配单个元素:

You might want to try find_in_set(). It is more accurate that a LIKE match, since it matches on individual elements only:

SELECT u.firstname, u.lastname, u.address user_address, a.*
FROM users u
INNER JOIN address_effect a 
    ON FIND_IN_SET(a.address, REPLACE(u.address, ', ', ','))

如果 address_effect(address) 可以在 users(address) 中存储的 CSV 列表中找到，则匹配.

This matches if address_effect(address) can be found in the CSV list stored in users(address).

这篇关于跨 2 个表查找部分字符串匹配的 SQL 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！