Mysql 在子选择中使用 GROUP_CONCAT

Question

我在 mysql 中的子选择有问题。在餐 table 餐厅中，我有“sup”字段，其中 ID 之间用逗号分隔。基本选择:

mysql> select name, sup from restaurants LIMIT 5;
+-------------------------------------+---------+
| name                                | sup     |
+-------------------------------------+---------+
| Pizzerija in špagetarija Buf        | 2,14,18 |
| EJGA - KAVARNA - RESTAVRACIJA - PUB | 11,17   |
| Restavracija Center                 | 5,22    |
| Restavracija Viola                  | 5,13,17 |
| Gostilna Anderlič                   | 5,17    |
+-------------------------------------+---------+
5 rows in set (0.00 sec)

我想知道suply表中的字段“SI”以获取sup.restaurants表中的ID。所以我的选择是:

mysql> SELECT GROUP_CONCAT(suply.SI SEPARATOR ', ')  FROM `suply` WHERE id IN (2,14,18);
+---------------------------------------+
| GROUP_CONCAT(suply.SI SEPARATOR ', ') |
+---------------------------------------+
| Italijanska, Špagetarija, Picerija    |
+---------------------------------------+
1 row in set (0.00 sec)

所以我用 subselct 写了 select 但效果不好:

mysql> SELECT restaurants.name,
    -> (SELECT GROUP_CONCAT(suply.SI SEPARATOR ', ')  FROM `suply` WHERE id IN (restaurants.sup)) AS hrana
    ->  FROM restaurants
    ->  LIMIT 5;
+-------------------------------------+--------------------+
| name                                | hrana              |
+-------------------------------------+--------------------+
| Pizzerija in špagetarija Buf        | Italijanska        |
| EJGA - KAVARNA - RESTAVRACIJA - PUB | Mednarodna kuhinja |
| Restavracija Center                 | Slovenska domača   |
| Restavracija Viola                  | Slovenska domača   |
| Gostilna Anderli?                   | Slovenska domača   |
+-------------------------------------+--------------------+
5 rows in set (0.00 sec)

为什么在这个选择中我只得到第一个字符串？

Answer 1

使用FIND_IN_SET在逗号分隔列表中搜索的函数

WHERE FIND_IN_SET(id, restaurants.sup)