为什么使用另一张表的筛选结果，放到where id in （）里面，结果不对？

为什么使用另一张表的筛选结果，放到where id in （）里面，结果不对？

比如现在固定筛选，5514，5515这两个id是可以的。

mysql> select id from terminal where id in (5514,5515);
+------+
| id   |
+------+
| 5514 |
| 5515 |
+------+
2 rows in set (0.08 sec)

另一张表的某个json里面有个字段，记录的这个id表，也没问题，能查出来5514，5515

mysql> select pgrpdevlist ->> '$.devicelist' from playproject where id = 13;
+--------------------------------+
| pgrpdevlist ->> '$.devicelist' |
+--------------------------------+
| 5514,5515                      |
+--------------------------------+
1 row in set (0.06 sec)

但是两个结合起来就有问题了，只能读出来一个。

mysql> select id from terminal where id in (select pgrpdevlist ->> '$.devicelist' from playproject where id = 13);
+------+
| id   |
+------+
| 5514 |
+------+
1 row in set (0.09 sec)

mysql> select pgrpdevlist ->> '$.devicelist' from playproject where id = 13;
这个结果是 5514,5515 数据类型是varchar吧、
所以mysql> select id from terminal where id in (select pgrpdevlist ->> '$.devicelist' from playproject where id = 13);
= select id from terminal where id in (‘5514,5515’);

试试对 select pgrpdevlist ->> '$.devicelist' from playproject where id = 13; 查出来的结果字符串进行分割。
参考 https://blog.csdn.net/qq_38932871/article/details/119789239

还是不行。

mysql> select SUBSTRING_INDEX( (select pgrpdevlist ->> '$.devicelist' from playproject where id = 13),',',2);
+------------------------------------------------------------------------------------------------+
| SUBSTRING_INDEX( (select pgrpdevlist ->> '$.devicelist' from playproject where id = 13),',',2) |
+------------------------------------------------------------------------------------------------+
| 5514,5515                                                                                      |
+------------------------------------------------------------------------------------------------+
1 row in set (0.15 sec)

mysql> select id from terminal where id in ( SUBSTRING_INDEX(( select pgrpdevlist ->> '$.devicelist' from playproject where id = 13),',',2 ));
+------+
| id   |
+------+
| 5514 |
+------+
1 row in set (0.16 sec)

mysql> 

你要么构造一个字符串，内容是(5514,5515)
要么查询出来两行，
5514
5515
你现在查到的这玩意不伦不类呀
pgrpdevlist ->>是个什么写法，为什么非要这样
直接select id from不行吗

你这个当然不对呀，你发现你这个语句了没有，select pgrpdevlist ->> '$.devicelist' from playproject where id = 13 ，你这是一条数据呀，pgrpdevlist是一个值，你这个pgrpdevlist 的值是5514,5515，你不要把pgrpdevlist 当做两条数据来看待，你把拿到的数据都理解错了。