SQL 語法 – Join on Partial match of Strings

測試環境為 CentOS 8 x86_64 虛擬機.

多個資料表單有相對應的欄位時,我們可以透過 JOIN 來合併查詢多個資料表單的資料, 請可以參考 – https://benjr.tw/101855 , Join 時 ON 比較資料都是使用完整欄位當作對應,有辦法使用部分的資料來 Join 嗎? 可以的來看一下下面範例.

[root@localhost ~]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 25
Server version: 5.5.60-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> CREATE DATABASE testdb;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> USE testdb;
Database changed

下面範例有兩個 Table.

1. url (存放完整 URL 位址)

   MariaDB [testdb]> CREATE TABLE url (K1 INT(11) NOT NULL AUTO_INCREMENT , url VARCHAR(200) NOT NULL , PRIMARY KEY (K1));
   Query OK, 0 rows affected (0.02 sec)
   

   在 url 資料表建立資料.

   MariaDB [testdb]> INSERT INTO url (url) VALUES('https://www.google.com') , ('https://www.yahoo.com') , ('https://www.google.com.tw/maps') , ('https://www.microsoft.com') , ('https://www.microsoft.com/zh-tw/microsoft-teams/') , ('https://www.microsoft.com/zh-tw/microsoft-365') ;
   Query OK, 6 rows affected (0.005 sec)
   Records: 6  Duplicates: 0  Warnings: 0
   
   MariaDB [testdb]> SELECT * FROM url;
   +----+--------------------------------------------------+
   | K1 | url                                              |
   +----+--------------------------------------------------+
   |  1 | https://www.google.com                           |
   |  2 | https://www.yahoo.com                            |
   |  3 | https://www.google.com.tw/maps                   |
   |  4 | https://www.microsoft.com                        |
   |  5 | https://www.microsoft.com/zh-tw/microsoft-teams/ |
   |  6 | https://www.microsoft.com/zh-tw/microsoft-365    |
   +----+--------------------------------------------------+
   6 rows in set (0.000 sec)
   

2. company (存放公司名稱)

   MariaDB [testdb]> CREATE TABLE company (K1 INT(11) NOT NULL auto_increment , name VARCHAR(100) NOT NULL , PRIMARY KEY (K1));
   Query OK, 0 rows affected (0.02 sec)
   

   並在 company 建立資料.

   MariaDB [testdb]> INSERT INTO company (name) VALUES('google') , ('yahoo') , ('microsoft');
   INSERT INTO company (name) VALUES('google') , ('yahoo') , ('microsoft');
   Query OK, 3 rows affected (0.001 sec)
   Records: 3  Duplicates: 0  Warnings: 0
   
   MariaDB [testdb]> SELECT * FROM company;
   +----+-----------+
   | K1 | name      |
   +----+-----------+
   |  1 | google    |
   |  2 | yahoo     |
   |  3 | microsoft |
   +----+-----------+
   3 rows in set (0.000 sec)
   

company 為 url 的部分資料,要怎麼透過 JOIN 把兩邊資料對比出來.這邊利用 Inner Join 但使用 LIKE 並搭配 %name% 方式來合併兩邊資料.

SELECT c.name , u.url
FROM url u
INNER JOIN company c ON u.url LIKE concat('%', c.name , '%');

MariaDB [testdb]> SELECT c.name , u.url
    -> FROM url u
    -> INNER JOIN company c ON u.url LIKE concat('%', c.name , '%');
+-----------+--------------------------------------------------+
| name      | url                                              |
+-----------+--------------------------------------------------+
| google    | https://www.google.com                           |
| yahoo     | https://www.yahoo.com                            |
| google    | https://www.google.com.tw/maps                   |
| microsoft | https://www.microsoft.com                        |
| microsoft | https://www.microsoft.com/zh-tw/microsoft-teams/ |
| microsoft | https://www.microsoft.com/zh-tw/microsoft-365    |
+-----------+--------------------------------------------------+
6 rows in set (0.004 sec)