SQL在两个表上加入并替换和返回列

发布于 2025-02-14 00:32:09 字数 1983 浏览 7 评论 0原文

我正在使用JONINS进行一些SQL查询，并想从与原始表列值匹配的另一个表中获取相应的值。

例如，我有两个名为产品和类别的不同表格，它们如下：

产品：

| 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中有此blots＆amp; cockesore〜：/id1一种存储在列名称下的无效字符串category1， category2和category3，我想从表类别中替换为有效的类别名称。如果您查看表类别每个类别的ID与字符串botbers＆amp; cossecories＆amp; costausie〜：/id1在其中id1。

例如： 布置＆amp;附件〜：/ID1在此字符串id1中与表category属于bloths＆amp;附件

我必须替换并返回所有category1，category2和category3 table code> product> product 的列使用有效的类别。

为此，最佳的SQL加入查询是什么？

原文

I am working on some SQL query with joins and I want to get the corresponding value from another table which matches with original table columns values.

For example, I have two different tables named Product and Category and they are as follows:

Products:

| 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|
|----|---------|---------------------------|------------- |-------------|

And Category:

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

The problem is, in table Product there is this Cloths&Accessories~:/ID1 kind of Invalid strings stored under column names category1, category2 and category3 and I want to replace them with valid category names from table Category. If you look at the table Category the id of each category matches with string Cloths&Accessories~:/ID1 having ID1 in it.

For example:
Cloths&Accessories~:/ID1 in this string ID1 is related to id=1 of table Category which is Cloths & Accessories

I have to replace and return all of category1, category2 and category3 columns of table Product with valid categories.

What would be the optimal SQL join query for this?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

眸中客 2025-02-21 00:32:09

您可以使用提取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

You could use substring_index to extract the ID, and then join on it:

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

回复收藏 0 原文

~没有更多了~