使用CSV从另一个表中的B列获取表A列中的值

Hello friends I have two tables like so:

朋友们，我有两张这样的桌子:

Table Items
+--------------------+
| id | item          |
+----+---------------+
| 1  | Table         |
+----+---------------+
| 2  | couch         |
+----+---------------+
| 3  | Bed           |
+----+---------------+
| 4  | Chair         |
+----+---------------+
| 5  | Desk          |
+----+---------------+

Table Acme
+--------------------+--------+
| id | items         | Client |
+----+---------------+--------+
| 1  | 1,3,5         | S45-56 |
+----+---------------+--------+

What I need is to use CSV from Acme table to select items in Items table

我需要从Acme表中使用CSV来选择items表中的项!

I tried using the following query but I only get the first of the tree values

我尝试使用下面的查询，但只得到树的第一个值

SELECT * FROM Items WHERE id IN (SELECT items FROM Acme WHERE client ='S45-56')

Any idea?

任何想法?

Thank in advance.

提前感谢。

1 个解决方案

#1

You can use find_in_set() to check if a value is in a comma separated list.

您可以使用find_in_set()来检查一个值是否在逗号分隔的列表中。

SELECT i.*
       FROM items i
            INNER JOIN acme a
                       ON find_in_set(i.id, a.items)
       WHERE a.client = 'S45-56';

But it's best to fix your schema and don't use comma separated lists but multiple rows in a table instead.

但是最好修复您的模式，不要使用逗号分隔的列表，而是在一个表中使用多个行。

#1