我们可以尝试以下连接查询:
SELECT
i.code,
i.total,
COALESCE(e.name, m.name) AS name
FROM inventory i
LEFT JOIN electricity e
ON i.code REGEXP CONCAT('el_.*_', e.id, '$')
LEFT JOIN mobile m
ON i.code REGEXP CONCAT('mob_.*', m.id, '$');
Demo
上面的查询使用COALESCE技巧为每个项目选择正确的名称,这假定给定项目仅匹配电力或移动表格.
但是,您的数据库设计并不理想.只有一个包含移动和电子(和其他)项目元数据的表会好得多.另外,您的表应该具有正确的连接列,这些连接列不需要复杂的子字符串或正则表达式操作来匹配.我建议如下:
inventory
+----+------------+-----------+
| id | code | total |
+----+------------+-----------+
| 1 | el_pr_25 | 45 |
| 2 | el_pr_11 | 33 |
| 3 | el_pr_x73 | 25 |
| 4 | mob_tp_x93 | 23 |
| 5 | mob_tp_t55 | 33 |
| 6 | mob_tp_25 | 22 |
+----+------------+-----------+
items
+--------------+----------+-------------+
| inventory_id | name | type |
+--------------+----------+-------------+
| 1 | test1 | electricity |
| 2 | test2 | electricity |
| 3 | test3 | electricity |
| 4 | test 66 | mobile |
| 5 | test 222 | mobile |
| 6 | test 323 | mobile |
+--------------+----------+-------------+