56,914
社区成员




-- 懒得到写函数、存储过程啦,用JOIN 整好啦!谢谢提醒!
create table t1(id int, names varchar(255));
create table t2(id int);
insert into t1(id,names) values(1,'luoyoumou1,luoyoumou2,luoyoumou3,luoyoumou4');
insert into t1(id,names) values(2,'lili1,lili2,lili3,lili');
insert into t2(id) values(1);
insert into t2(id) values(2);
insert into t2(id) values(3);
insert into t2(id) values(4);
insert into t2(id) values(5);
select t1.id, t2.id id2, reverse(substring_index(reverse(substring_index(concat(t1.names,','),',',t2.id)),',',1)) names
from t1, t2
where reverse(substring_index(reverse(substring_index(concat(t1.names,','),',',t2.id)),',',1))<>''
order by t1.id, t2.id;
-- 比如说:我现在有表 t, t2,如下:
root@localhost(test)10:34:29>select * from t;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)
root@localhost(test)10:34:32>select *from t2;
+------+---------------------------------------------+
| id | names |
+------+---------------------------------------------+
| 1 | luoyoumou1,luoyoumou2,luoyoumou3,luoyoumou4 |
| 1 | lili1,lili2,lili3,lili4 |
+------+---------------------------------------------+
2 rows in set (0.00 sec)
-- 请问:用JOIN语句的话,怎么写呢?