56,687
社区成员
发帖
与我相关
我的任务
分享
select a.id,b.id,b.name
from a,b
where FIND_IN_SET(b.id,a.B_ids)
order by a.id,b.id;
mysql> use world;
Database changed
mysql> create table A(id int,B_ids varchar(30));
Query OK, 0 rows affected (0.31 sec)
mysql> insert into A values(148,'1,2,3'),(151,'2'),(152,'3'),(153,'0');
Query OK, 4 rows affected (0.05 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> create table B(id int,name varchar(50));
Query OK, 0 rows affected (0.40 sec)
mysql> insert into B values(1, '四段7-47号'),(2,'三段22-22号'),(3,'四段6-133号'),(4,'三段22-19号'),(5, '二段24-109号');
Query OK, 5 rows affected (0.07 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select B.* from A , B where concat(',',A.B_ids,',') like concat('%,',cast(B.id as char),',%') order by A.id,B.id;
+------+-------------+
| id | name |
+------+-------------+
| 1 | 四段7-47号 |
| 2 | 三段22-22号 |
| 3 | 四段6-133号 |
| 2 | 三段22-22号 |
| 3 | 四段6-133号 |
+------+-------------+
5 rows in set (0.00 sec)
mysql>