SQL连接两个表，并替换和返回列

Question

我正在处理一些SQL查询连接，我想从另一个表中获得与原始表列值匹配的相应值。

例如，我有两个名为Product和Category的不同表，它们如下:

产品:

| id | name    | category1                 | category2    | category3   |
| -- | ------- | ------------------------- |--------------------------- |
| 1  | T-shirt | Cloths&Accessories~:/ID1  | Cloths~:/ID2 | Shirts~:/ID3|
|----|---------|---------------------------|------------- |-------------|
| 2  | T-shirt | Cloths&Accessories~:/ID1  | Cloths~:/ID2 | Shirts~:/ID3|
|----|---------|---------------------------|------------- |-------------|
| 3  | T-shirt | Cloths&Accessories~:/ID1  | Cloths~:/ID2 | Shirts~:/ID3|
|----|---------|---------------------------|------------- |-------------|
| 4  | T-shirt | Cloths&Accessories~:/ID1  | Cloths~:/ID2 | Shirts~:/ID3|
|----|---------|---------------------------|------------- |-------------|
| 5  | T-shirt | Cloths&Accessories~:/ID1  | Cloths~:/ID2 | Shirts~:/ID3|
|----|---------|---------------------------|------------- |-------------|

和类别:

| id | categories           |
| -- | -------------------- |
| 1  | Cloths & Accessories |
|----|--------------------- |
| 2  | Cloths               |
|----|--------------------- |
| 3  | Shirts               |
|----|--------------------- |

问题是，在表Product中，有Cloths&Accessories~:/ID1种无效字符串存储在列名称category1,category2和category3下，我想用表Category中的有效类别名称替换它们。如果您查看表Category，则每个类别的id与包含ID1的字符串Cloths&Accessories~:/ID1匹配。

例如:字符串ID1中的Cloths&Accessories~:/ID1与表Category中的id=1相关，即Cloths & Accessories

我必须用有效的categories替换并返回表Product的所有category1,category2和category3列。

最优的SQL连接查询是什么?

Answer 1

您可以使用substring_index提取ID，然后在其上加入:

SELECT p.id AS id,
p.name AS name,
c1.categories AS category1,
c2.categories AS category2,
c3.categories AS category3
FROM   products p
JOIN   category c1 ON SUBSTRING_INDEX(p.category1, 'ID', -1) = c1.id
JOIN   category c2 ON SUBSTRING_INDEX(p.category2, 'ID', -1) = c2.id
JOIN   category c3 ON SUBSTRING_INDEX(p.category3, 'ID', -1) = c3.id