mysql - 如何将Mysql的一行显示为多行？

Question

我需要一些帮助。我有这个简单的 mysql 查询:

select '1,2,3,4,5,6' as Col1 from dual;

和表1:

Col1
1,2,3,4,5,6

我还有另一个表2:

service_id    service_name
1             Service1
2             Service2

我尝试了下一个查询但不起作用:

select service_name from table2 where service_id in (select col1 from table1)

如有任何帮助，我们将不胜感激!

Answer 1

试试这个:

示例数据:

create table `dual` (Col1 varchar(100));
insert into `dual` values ('1,2,3,4,5,6');

create table table2 (service_id int, service_name varchar(100));
insert into table2 values
(1, 'Service1'),
(2, 'Service2');

T-SQL:

select service_name from table2 t2
where exists(
    select 1 from `dual` d
    where locate(concat(',', t2.service_id, ','), concat(',', d.Col1, ',')) > 0
);